Need VBA Macro to change subform SouceObject/Master/Child fields

[chip0105]

In my subform I have a [DueDate] field. I want to find project records that are not past due as well as the records that have been completed On-Time.

I created a query that works and displays all the data I need.

How can I use VBA code to change my SouceObject/Master/Child fields to another data source so I can use the same form and subform to perform multiple filtering functions?

:banghead: Any assistance would be greatly appreciated.

 

[MarkK]

Do you need to change the subform to a different form? If so, you simply change the name of the SourceObject property of the Subform control to the name of the form you want to load. So if your subform control is called SFM, then you can load the fOrderSFM subform using code like . . .

Me.SFM.SourceObject = "fOrderSFM"

Watch out though if you do this programmatically, because Access will automatically update the LinkMasterFields and LinkChildFields properties of the subform control if it can find a relationship between the parent and child data sources. That may not be what you intend to do.

But this is also not how you'd apply a filter on a thing like a date, which commonly needs to be compared to a range of other dates. LinkMasterFields and LinkChildFields rely on equality between the primary key in a parent row, and a foreign key in the child rows, so you can't filter for a range of values using this method. To filter the subform, apply a filter, or change out the recordsource. Check out the Form.Filter, Form.FilterOn, and Form.RecordSource properties for more info.

Hope this helps,