how to check if subform is opened in ms access

Question

I have a main form MYMAIN with two subforms in it MYSUBONE and MYSUBTWO.

I have "on current" events in each subform that update textbox in the other subform.

My problem arises when the forms are being loaded. The "on current" event is triggered when the subform "MYSUBONE" is loaded (BEFORE "MYSUBTWO" is loaded) and it tries to update a textbox in MYSUBTWO which is still not yet loaded. So error is triggered in the event procedure.

How do I check in my "on current" event procedure (in VBA?) for MYSUBONE to check if the MYSUBTWO subform is not yet loaded.

on-current-mysubone if mysubtwo is not loaded then update mysubtwo.textbox = ... end if

I tried the "Isloaded" function in the sample database "Northwind" but doesn't seem to work. how do i check if subform is not yet loaded?

Or could I just ignore error and use something like "if error, exit function"?

Answer 1

Subforms are actually opened before the main form is opened (including all their OnCurrent events). I don't think you can guarantee which order they are loaded in either.

I'd turn the problem around and do the update from the main form. If you really have to do the update from the Subform, move the update to a separate function in MySubOne. Then in the main form's OnCurrent, call the function in MySubOne. This will guarantee that both MySubOne and MySubTwo are already loaded.

Answer 2

Hi rtoChip,
One possible way to solve this problem would be to just ensure you know what order the subforms are loaded in. You can accomplish this by unbinding the subform controls and then manually loading them. Here is how to do it:

1. Add your subform controls to the parent form as normal.
2. Make sure you have your link fields, etc. set up how you want them.
3. In the subform properties>data tab, delete the value in the "Source Object" Field.

Add VBA to manually bind the controls when the parent form opens:

Private Sub Form_Open(Cancel As Integer)
    Me.sfB.SourceObject = "FormB"
    Me.sfA.SourceObject = "FormA"
End Sub

Answer 3

"is there a way to check if the retrieved subforms record count is 0"

if forms!myMainForm!mySubForm.form.RecordsetClone.RecordCount = 0 then....