Application-defined or object defined error setting subform record source

0
votes

I am trying to set the recordset of a subform that is part of a parent form.

The code that I am using is:

docmd.openform "mainFrm"
Forms!mainFrm.subFrm.Form.RecordSource = "SELECT * FROM table1 WHERE ID = 24;"

I get the following error:

Application-defined or object defined error

If I change the vba to:

docmd.openform "subFrm"
Forms!subFrm.RecordSource = "SELECT * FROM table1 WHERE ID = 24;"

The subform opens fine.

One thing that I have noticed is that even though SubFrm is part of the mainFrm. Even when the mainFrm is open:

?CurrentProject.AllForms("SubFrm").IsLoaded

returns false

Even though

 ?CurrentProject.AllForms("MainFrm").IsLoaded

returns true

Is there a way to change the recordset of a sub form that is part of a main form while the main form is open?

1
ms-accessvba
Why? If you link your main and sub form there's no reason to. – Rene
The name of the sub form itself and the name of the sub form control on the main form are the same as it seems. Maybe this is the reason? – Unhandled Exception
@June7 You are right. The name of the container control was child0. I changed it's name to SubFrm and everything is working now. Thanks! – jedu

1 Answers

1
votes

Subforms are not opened as independent forms and therefore cannot be included in the active Forms collection. Only active independent forms are listed in the Forms collection and return True with the IsLoaded property.

Referencing subform and its controls and properties must be done via the subform container control that holds object (table, query, form, report). If subFrm is name of the form, what is name of container control that holds the form? I usually name container different from the object it holds, such as ctrDetails. Then referencing the subform RecordSource property from code outside that subform would be:

Forms!mainFrm.ctrDetails.Form.RecordSource = "SELECT * FROM table1 WHERE ID = 24;"