MS Access / VBA Tip
Access Subform Observations
Just a quick note based upon some observations I made this past month:
Access subforms are loaded before the Form_Load event on the main form. Linked subforms are synchronized with the main form before the Form_Current event fires on the main form.
Implications: If you want to do some processing on a main form before subforms do their thing, leave blank the subform control’s SourceObject. Insert the SourceObject at the end of the main form’s Form_Load event. I sometimes do this when an OpenArg determines something important about subform usage.
A linked subform knows what the main form’s current record is before the VBA programmer does. If you need to know what the current record is before the subform does, you must eliminate Master/Child links in the subform control’s property sheet. Then just reset the subform’s RecordSource property with an updated SQL statement at the end of the main form’s Form_Current event. Setting the subform’s RecordSource property forces a subform requery, thus keeping it up-to-date. There are, of course, other ways to do this, but this seems the most direct.