chaning subform recordsource makes subform change to single form

[reginaldmerritt]

Has anyone had this problem before, my search on-line would suggest it not common but I'm sure you fine folk will be able to help.

This is the code I have used to change the recordsource on a subform. I have similar code to order the data using different queries.

Private Sub OrderByStartDate_Click()
Forms.FRMClaims.ClaimsDisplaySubForm.Form.RecordSource = "QYClaimsByStartDate"
End Sub

However, when i run this bit of code the subform only displays one record.

Ok so first thought would be that the query used is causing the problem. However, the subform opens using the very same query as the one mentioned in the code above. Also opening the query itself shows all the records.

I can't for the life of me work out why it suddenly changes to a single record when using the code above.

Are there any property settings that I should be checking, any ideas on what could be causing this problem?

Any suggestions would be much appreciated.

[ChipR]

Have you tried using the .Form.OrderBy property? I don't see what the problem could be at the moment, but this may be a good idea anyway.

[reginaldmerritt]

Thanks ChipR, thats a great idea for some of what i want to do, but for others i need to use a specific query.

I can't seem to set the OrderBy property for the subform.

This is the code i'm using, but i doen't seem to do anything.

Private Sub OrderByPlannedEndDate_Click()
Forms.FRMClaims.ClaimsDisplaySubForm.Form.OrderBy = "[PlannedEndDate]"
End Sub

[ChipR]

It turns out if your underlying subform has its recordset defined including an order by clause, you can't override it.

[reginaldmerritt]

Oh well, nice idea though.

[ChipR]

Then again, perhaps you just need to add:

Forms.FRMClaims.ClaimsDisplaySubForm.Form.OrderByOn = True

[reginaldmerritt]

Hey that works, well done ChipR

[reginaldmerritt]

Turns out that i had the main form set to the same record source as the subform, just needed to remove the record source on the main form for the subform to work.

Don't know how i ended up with the main form using a recordsource, perhaps i should pay more attention to which forms properties i'm editing.

Thanks to ChipR suggestion i was able to also remove some of the quires i set up and Order the data though code rather than a query. This is particularly handy when using a where statement with an OpenForm method, as changing the record source will negate the where statement.