Form/Subform issue

[JasperQ]

Is there a way to stop subforms from loading until a specific main form control has a value? I need a certain value to be set so that I can filter the query results for several different subforms.


ETA: I've added much more information below for anyone who finds it helpful to see what it is I'm try to accomplish and why. Feel free to disregard if you want as the above question is what it boils down to (I think?).

I'm probably making this much more complicated than it needs to be, but here goes...

I'm designing a time sheet database. Because of the layout of the form, which the user wants to look like a traditional time sheet, I decided the best way to achieve that and still keep a normalized table was to use a form/subform(s) setup. Tables involved are tblEmployees and tblTimeSheets. Main form (frmTimeMain) has employee ID, an unbound date field for the week ending date (txtWkEnding) which the user fills out via the DatePicker, and 7 other unbound date fields (txtSuDate, txtMDate, etc.) that get their value once the user picks the week ending date. I then put code in the OnChange event of txtWkEnding so that once the user picks the date and the 7 unbound controls get their values, each of the subforms (sbfChildSu, sbfChildM, etc.) date fields (which are not visible to the user) also get their values from the corresponding unbound field on the main form. So if the user chooses 10/21/2017 as the Week Ending Date, the value of parent form control txtSuDate will be 10/15/17 and the value of sbfChildSu's txtDate control will also be 10/15/17.

This works great if I want to set everything to only allow new records. But it occurred to me as I was testing things out that someone might need to actually edit those time sheets on occasion (duh). Problem though...when I reopen the form, every record shows up in every subform. So then I thought - Hey! I'll just change the RecordSource of the subforms to queries and then I can set criteria to filter the data so that only records with dates that match the corresponding main form date fields will be in each subform. So the underlying query for sbfChildSu's criteria will look for and filter records by what's in the main form's txtSuDate field, etc. That won't work though, because the subforms load before the user sets the dates.

 

[CJ_London]

Alternative in the main form either leave the sourceobject for each subform blank or set to a 'blank' form with no fields or code.

then in the main form current event (or some control after update event) use vba code to populate each subform sourceobject as required.

Another way is to have subform form with a recordsource to return no values such as

select * from mytable where true=false

then modify the recordsource accordingly on the current or control event

And similar, set the subform form filter to 'false' or 0 and filter on load to true. then on the mainform current /control event set it to ""

You need to make sure you save these settings so they are fixed for every time the form are opened - and users should not be able to mess with the forms

 

[JasperQ]

Thank you both for taking the time to respond. I'll try the options given as well as watch the tutorials tomorrow and let you know how it all goes. Again, much thanks!