Tutorial :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"?


Solution:1

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  


Solution:2

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.


Solution:3

Don't know if it is yet useful, but for what's worth... I'm displaying two grids with parent-child relationship. However, second grid displays at once all child records associated to all parent records in first grid. When one record is selected in first grid, I want to locate first corresponding child record in the second grid. I faced the same trouble as reference to second grid's (subform's) recordset fails when initially opening main form, as second grid has not yet been displayed. My workaround consisted on catching error 2455 and ignoring it, so it gives chance to continue until second grid is fully displayed.

Private Sub Form_Current()  Dim rsResults As Recordset  Dim stFilter As String    On Error GoTo ErrHdlr    'Locate first results record associated to current requisition  Set rsResults = Me.Parent.sfResults.Form.Recordset  stFilter = obRecord.GetFilter(Me.Recordset, vrPKFields, vrPKTypes)  rsResults.FindFirst (stFilter)    Exit Sub    ErrHdlr:  If Err.Number = 2455 Then     Exit Sub 'When this sub form is first displayed, Current event                 'tries to reference another subform on the same parent.                 'However, such second subform is not yet open and                 'reference fails. This is the error number raised,                 'so it is ignored on purpose. After second subform                 'is displayed, code works correctly.                 'It had to be handled this way because there's no way                 'to know in advance whether subform is already open.  Else     msgbox Err.number & " " & err.description  endif  exit sub  


Solution:4

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

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

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »