i have the code below on subform1 of a form. this refers to unrelated subform2 (fzzzSubConditionalFormatCriteria) on the same form.

on opening of the main form i get the ": Form : <You entered an expression that has an invalid reference to the property Form/Report.> : Form" error, but after that it works fine when i'm clicking through records. how do i get around this? or, as the worst case scenario, jsut don't run this code on opening of the form

Basically, need a way to tell when a subform loaded and can be accessed

  vrSQL = "SELECT zzAppObjectFields.AppObjectField, zzAppObjectFields.AppObjectFieldTypeID, zzAppObjectFields.AppObjectIDCmb " & _
                            "FROM zzAppObjectFields WHERE zzAppObjectFields.AppObjectFieldTypeID In (10,12) AND zzAppObjectFields.AppObjectID=" & Me.AppObjectID & " " & _
                            "ORDER BY zzAppObjectFields.AppObjectField;"
    
                Me.Parent.fzzzSubConditionalFormatCriteria.Form!uuulllAppObjectFieldIDCriteria.RowSource = vrSQL
                Me.Parent.fzzzSubConditionalFormatCriteria.Form!uuulllAppObjectFieldIDCriteria.Requery

i have the code below on subform1 of a form. this refers to unrelated subform2 (fzzzSubConditionalFormatCriteria) on the same form.

on opening of the main form i get the ": Form : <You entered an expression that has an invalid reference to the property Form/Report.> : Form" error, but after that it works fine when i'm clicking through records. how do i get around this? or, as the worst case scenario, jsut don't run this code on opening of the form

Basically, need a way to tell when a subform loaded and can be accessed

  vrSQL = "SELECT zzAppObjectFields.AppObjectField, zzAppObjectFields.AppObjectFieldTypeID, zzAppObjectFields.AppObjectIDCmb " & _
                            "FROM zzAppObjectFields WHERE zzAppObjectFields.AppObjectFieldTypeID In (10,12) AND zzAppObjectFields.AppObjectID=" & Me.AppObjectID & " " & _
                            "ORDER BY zzAppObjectFields.AppObjectField;"
    
                Me.Parent.fzzzSubConditionalFormatCriteria.Form!uuulllAppObjectFieldIDCriteria.RowSource = vrSQL
                Me.Parent.fzzzSubConditionalFormatCriteria.Form!uuulllAppObjectFieldIDCriteria.Requery

• vba
• ms-access

• 2 Believe it or not, I ran into this exact issue just yesterday. My workaround was to remove both subforms from the main form and then save and close the main form. Then I re-opened the main form in design view, added the subforms back in reverse order of their usage (based on code), and everything resolved itself. Before doing that, try a decompile followed by a compact and repair. Then go back into the VBE and compile your code. – BobS Commented Mar 29 at 21:22
• I had a feeling the order of pasting them worked. Thank you lol. But I'm also curious if there's a code way to reach that object and see if it's open – lalachka Commented Mar 29 at 21:58
• 1 Subforms load before main form and apparently order of subform loading depends on order added to main form in design. I don't think code in subform1 form events will be able to reference subform2 unless subform2 actually loads first. – June7 Commented Mar 29 at 22:05
• 2 You can try referencing a control on your subform as per your code but also include an error trap. – BobS Commented Mar 29 at 22:11
• Huge thank you!! It worked. But how can I check is the subform loaded? – lalachka Commented Mar 30 at 11:41

1 Answer 1

In answer to your last comment, you can check the load status of your subform with the following code which should be placed into a module:

Option Explicit

Public Function SubformNotLoaded(strFormName As String, _
                                 strSubFormName As String) As Boolean

  Dim frm As Access.Form

  On Error Resume Next

  ' Initialise return value.
  SubformNotLoaded = False

  ' Attempt to reference the subform
  Set frm = Forms(strFormName).Controls(strSubFormName).Form

  ' An error is thrown if the subform has not yet been fully loaded.
  If Err.Number <> 0 Then
    ' Set the return value.
    SubformNotLoaded = True
  End If

  ' Housekeeping.
  Err.Number = 0
  Set frm = Nothing

End Function

You can then call this function from anywhere in your code as follows by simply passing in the name of the form that contains the subform and also the name of the subform itself:

  If SubformNotLoaded("frmForm", "frmSubForm2") = True Then
    MsgBox "Subform not fully loaded."
  Else
    MsgBox "Subform is fully loaded."
  End If