Solved

Changing subform source object and refreshing

Posted on 2010-11-16
10
2,200 Views
Last Modified: 2012-05-10
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"
Forms!frm_Manager_Dashboard!frm_ManagerTasks.Requery

End Sub

Thanks in advance.
0
Comment
Question by:The_Hitcher
  • 6
  • 4
10 Comments
 
LVL 84
ID: 34144094
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?
0
 

Author Comment

by:The_Hitcher
ID: 34144126
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.
0
 
LVL 84
ID: 34144186
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?

0
 

Author Comment

by:The_Hitcher
ID: 34144233
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.
0
 

Author Comment

by:The_Hitcher
ID: 34144250
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()
Forms!frm_Manager_Dashboard!frm_ManagerTasks.Requery
End Sub

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 84
ID: 34144253
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 & "#"
0
 
LVL 84
ID: 34144261
And: Where are the values of "StartDate" and "EndDate" coming from?
0
 
LVL 84
ID: 34144366
You must Requery the Form object:

Forms!frm_Manager_Dashboard!frm_ManagerTasks.Form.Requery

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:

?Me.Recordsource
0
 

Author Comment

by:The_Hitcher
ID: 34144717
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
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34144999
I see. You may be running into an issue where your Subform's recordsource (the query) loads before the mainform's combo is filled (i.e. the timing issue). Using tightly coupled queries (i.e. a query coupled directly to a Form, as is yours) can lead to some odd behaviors. Better to build the query WITHOUT any form references, and then let either (a) your Master/Child links manage the data return or (b) set the Filter directly or (c) Set the subform's Recordsource directly.

Since you're dealing with a subform that is NOT directly related to your parent records, it would seem that either (b) or (c) would be most relevant.

Also, check again to be sure that Access hasn't "done you a favor" and set the Master/Child link fields.


0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now