filtering a subform - when is a subform 'loaded'?

[Keith Nichols]

I have an SQL query that filters a subform based on the selected item in an option frame that runs on the after update event of the option frame.

Forms!fsubPrjPersonnel.RecordSource = SQLText & WClause

When I try this out, I get the following error message:

2450 - xxx can't find the form 'fsubPrjPersonnel' referred to in a macro expressions or Visual Basic code.

I then ran the following snippet and found that the form is not recognized as being loaded when it is "open" as a subform

If IsLoaded("fsubPrjPersonnel") Then
            MsgBox "form found"
        End If

If I open the subform as a form as well as having the main form & subform open, i.e. open a second instance of the subform from the database window, it filters fine.

I have been able to requery the subform so where am I going wrong?

 

[RuralGuy]

When a form is displayed as a SubForm it is not in the Forms collection. Instead you need to reference the "SubForm" by going through the MainForm in the Forms collection. This link will help you with the syntax. Refer to Form and Subform properties and controls

 

[Keith Nichols]

Thanks RG.

I have seen that page before but didn't twig that my problem lay in the referencing. I'm sure that I wil be able to get it going tomorrow when back at the office.

Regards,

 

[RuralGuy]

Have a good one Keith!

 

[Keith Nichols]

problem solved - thanks again RG.

Tip for new(ish) players such as myself:

I saved the document word version of the reference sheet RG's link goes to and used the find and replace on the instructions at the top:

Mainform is the name of the top level form

Subform1 is the name of the subform CONTROL on mainform

Subform2 is the name of the subform CONTROL on the 1st subform.

to substitute my form & subforms names for Mainform, Subform1 etc. This makes the applying the information given much easier to get your head round.

 

[RuralGuy]

Hi Keith,
Thanks for posting back with your success.