MS Access 2007 subform reload on formheader = visible?

Question

I'm getting strange behavior in Access 2007 on a form designed in Access 2003.

I have an unbound main form with a data-bound subform. Within the Form_load of the main form, I set the subform sourceobject, which loads the subform recordset, etc. After the subform loads, but still within the Form_load of the main form, I set Me.FormHeader.Visible = True. After this line runs, for some reason the subform seems to close and reopen -- its Form_Unload fires, and it runs through its load process again.

Any idea why this would be happening? It's annoying, because its recordset is based on a parameter query, and the parameters get zapped when it reloads so it ends up prompting users for parameters.

I've gotten around it for the moment by doing the Me.FormHeader.Visible = True (and other main form display settings) before setting the subform sourceobject. But I still would like to know why setting a property on the main form causes the subform to reload, it makes no sense to me. — Eric G
I'm not sure about your exact situation, but with a subform bound to a bound parent form, the OnCurrent event of the child form first twice. Maybe there's a relationship with the problem you're encountering? — David-W-Fenton
Thanks, but my parent form is unbound so not exactly my situation. — Eric G
My point was that it could be happening with your situation, too. Have you investigated how often the child form's OnCurrent event is firing? — David-W-Fenton

HK1 HK1 · Accepted Answer · 2011-05-06T12:17:28

Sometimes you have to live with (and work around) the behavior of MS Access, no matter how strange or annoying it is.

In this case I would probably advise that you move away from a parameter query and instead prompt the user for their input using VBA, store their input in a form level variable, and use that input to set the recordsource during the subform's Form_Load event. This way the user will only be prompted one time and never need to know that the form actually loaded twice.

MS Access 2007 subform reload on formheader = visible?

1 Answers