Avatar of The_Hitcher
The_Hitcher asked on

Changing subform source object and refreshing

Hoping someone can advise on this one. I have some code that, when a button is clicked, activates a dropdown on a form and changes the source object of a subform. This is working ok, but after opening, the source object won't refresh. The data it displays is dependent on the value in the dropdown, which defaults to the current reporting period. The code is:

Private Sub cmd_View_By_Period_Click()

Dim var_Today As Date
var_Today = Date

Me.cmb_RepPeriod_MgmrTasks.Enabled = True
cmb_RepPeriod_MgmrTasks.Value = DLookup("Period_ID", "tbl_Rep_Period", "#" & Format(var_Today, "mm-dd-yyyy") & "# BETWEEN Start_Date AND End_Date")
Me.frm_ManagerTasks.SourceObject = "frm_ManagerTasks_Period"

End Sub

Thanks in advance.
Microsoft AccessSQL

Avatar of undefined
Last Comment
Scott McDaniel (EE MVE )

8/22/2022 - Mon
Scott McDaniel (EE MVE )

The data of a form being used as a Subform is dependant on two things:

1) The Recordsource of the form being used as a Subform
2) The Master/Child linking fields defined on the Subform Control in the parent form

Your code does nothing more than simply set the SourceObject (and BTW there is no need to Requery - this will occur when the SourceObject is loaded). Check the Recordsource and Master/Child link fields to insure they're setup correctly.

do you mean that you WANT the Subform data to be dependant on the cmb_RepPeriod_MgmrTasks value? If so, then either (a) filter the subform's recordsource or (b) apply a Filter to that subform. You'd do this AFTER opening the subform. Assuming you want to use the same filter as your DLookup, you'd do this:

Me.frm_ManagerTasks.Form.Filter =  Format(var_Today, "mm-dd-yyyy") & " BETWEEN Start_Date AND End_Date"
Me.frm_ManagerTasks.Form.FilterOn = True

I'm not sure where you're defining "Start_Date" and "End_Date", so be careful to insure that those actually have a Value. Are these controls on a form, or variables defined somewhere else?

Thanks for your reply. The subform source object that is loading uses a query as the datasource. The query in turn references the value selected in cmb_RepPeriod_MgmrTasks. I'm not explicitly setting any child/master link fields. The data that is shown on the subform is dependent on the value selected in cmb_RepPeriod_MgmrTasks. I've used this approach elsewhere and it has worked fine, but this is the only area where I have a button that changes the subform source object and the combo box that is being used. When opening the subform datasource outside of the master form (but with the master form open and a value selected in cmb_RepPeriod_MgmrTasks) it is working fine, but I can't get it to work within the subform window.
Scott McDaniel (EE MVE )

Could be a timing issue - the combo may not have fully populated before the subform control loads the form.

What's the reason for using a DLookup to set the value of the combo?

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

The table that is providing the data source for the master form does not include the field that I am using in the combo box, so I was unable to use the child / master approach. The DLookup is to set the value of the combo box to be the period that the current date is in.

Thanks, I'll have a look into the timing.

I do not think it is a timing issue. I have added an OnChange event procedure to the combo box itself. When the value is changed it still does not seem to be refreshing the subform. Is there an alternative to using Requery?

Private Sub cmb_RepPeriod_MgmrTasks_Change()
End Sub

Scott McDaniel (EE MVE )

Why not just set the Recordsource of the Subform directly, AFTER setting the SourceObject:

Me.frm_ManagerTasks.SourceObject = "frm_ManagerTasks_Period"
Me.frm_ManagerTasks.Form.Recordsource = "SELECT * FROM SomeQuery WHERE MyDateField BETWEEN #" & Date1 & "# AND #" & Date2 & "#"
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott McDaniel (EE MVE )

And: Where are the values of "StartDate" and "EndDate" coming from?
Scott McDaniel (EE MVE )

You must Requery the Form object:


However, I don't think this will solve your issue.

What is the Recordsource of that form after opening? Can you pust a breakpoint in the Current event of that subform, and then do this in the Immediate window:


I'm not sure what you mean. The record source is the query that uses the value in combo as a parameter. I've tried putting this code into the subform and it just shows that the query is the datasource.

Private Sub Form_Current()
Print Me.RecordSource
End Sub
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Scott McDaniel (EE MVE )

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question