Changing the Record Source of a Sub Form

marku24
marku24 used Ask the Experts™
on
I would like to change the Record Source of a sun form in MS access using a query.  This below is not working:

If FrameValues = 1 Then

    Form_navDashboard.frmSubCollectionTemplate_sub.Form.RecordSource = "sqryDashboardInputTable_ALL"
    Form_navDashboard.frmSubCollectionTemplate_sub.Requery
   
Else
    Form_navDashboard.frmSubCollectionTemplate_sub.Form.RecordSource = "sqryDashboardInputTable_FILTER"
    Form_navDashboard.frmSubCollectionTemplate_sub.Requery
   
End If
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Where are you doing this from?

If this is being done from some even on the NavDashboard form, then you should be able to use:

Dim strRecordSource as string
strRecordSource = "sqryDashboardInputTable_" & IIF(me.FrameValues = 1, "All", "Filter""
me.frmSubCollectionTemplate_sub.Form.RecordSource  = strRecordSource

Open in new window

You should not have to perform the requery, as soon as you change the subforms RecordSource it will automatically be requeried.

But if you are using navigation forms, then what you may have is a subform on a subform.  If this is the case, post back with a screen shot of your navigation form in design view, with the subform displayed that you want to edit the recordsource on.
John TsioumprisSoftware & Systems Engineer
Commented:
If FrameValues =1 Then
Form_frmSubCollectionTemplate_sub.RecordSource =  "sqryDashboardInputTable_ALL"
Else
Form_frmSubCollectionTemplate_sub.RecordSource =  "sqryDashboardInputTable_Filter"
End if

Open in new window

Author

Commented:
All seems to work, the correct query is recognized based on the toggle.  If i open the queries manually they are showing the correct data but i cannot get the screen to refresh the subform with the new query data.  I'm unable to upload files at this time.  

My subform named frmSubCollectionTemplate_sub is on my Dashboard named navDashboard.  The toggle is on the navDashboard form, not in the subform.  

Does that help?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
not really, navigation forms are hard to deal with, I never do unless I've inherited an application from someone.

It is far easier to resolve this type of issue when you can see the image, and then ask questions about the image.  Any chance you can simply post a copy of the database with only the part you are attempting to deal with, and only after sanitizing the data (replace names, phone numbers, addresses or any other personal or financial data).

We don't need the whole database, just the navform, the subform, the queries that are the Recordsource for the subform, and anything else that might cause errors when we load the form.

Author

Commented:
I actually got it to work on another subform, basically exactly the way you suggested.  Something must be wrong with my current form but i will figure that out now that i know the code.  Thank you for the help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial