469,091 Members | 1,178 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,091 developers. It's quick & easy.

chaning subform recordsource makes subform change to single form

reginaldmerritt
201 100+
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub OrderByStartDate_Click()
  2. Forms.FRMClaims.ClaimsDisplaySubForm.Form.RecordSource = "QYClaimsByStartDate"
  3. End Sub
  4.  
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.
Sep 17 '10 #1

✓ answered by ChipR

Then again, perhaps you just need to add:

Expand|Select|Wrap|Line Numbers
  1. Forms.FRMClaims.ClaimsDisplaySubForm.Form.OrderByOn = True

7 1652
ChipR
1,287 Expert 1GB
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.
Sep 17 '10 #2
reginaldmerritt
201 100+
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub OrderByPlannedEndDate_Click()
  2. Forms.FRMClaims.ClaimsDisplaySubForm.Form.OrderBy = "[PlannedEndDate]"
  3. End Sub
  4.  
Sep 17 '10 #3
ChipR
1,287 Expert 1GB
It turns out if your underlying subform has its recordset defined including an order by clause, you can't override it.
Sep 17 '10 #4
reginaldmerritt
201 100+
Oh well, nice idea though.
Sep 17 '10 #5
ChipR
1,287 Expert 1GB
Then again, perhaps you just need to add:

Expand|Select|Wrap|Line Numbers
  1. Forms.FRMClaims.ClaimsDisplaySubForm.Form.OrderByOn = True
Sep 17 '10 #6
reginaldmerritt
201 100+
Hey that works, well done ChipR
Sep 17 '10 #7
reginaldmerritt
201 100+
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.
Sep 18 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Maggieanp22 | last post: by
30 posts views Thread by Shannan Casteel via AccessMonster.com | last post: by
6 posts views Thread by ryan | last post: by
reply views Thread by robert.waters | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.