Referencing subforms

A

amiga1200

I am trying to access the record set of a subform on an active form, which
will change, using a string, when I try the following code, access reports
that it cannot find the form

Forms(frmName).Recordset.RecordCount

But when I reference it directly it works

Form_OldGroupsList.Recordset.RecordCount

but if i use this method does not allow dynamic access

Amiga1200
 
W

Wayne Morgan

A subform isn't "open" as a form. It is open as an object of the parent
form. There are actually two object you're dealing with. First, there is the
subform control on the parent form. This control holds the subform. This is
also where you set the parent/child link fields. The second object is the
subform being held in the subform control. The syntax to refer to a control
on the subform is

Forms!NameOfParentForm!NameOfSubformControl.Form!NameOfControlOnSubform

In the case of the recordset, it would be

Forms!NameOfParentForm!NameOfSubformControl.Form.Recordset.RecordCount

You can use Form!NameOfParentForm or the syntax you have when using the
variable frmName. That would change the above to

Forms(frmName)!NameOfSubformControl.Form.Recordset.RecordCount
 
M

Marshall Barton

amiga1200 said:
I am trying to access the record set of a subform on an active form, which
will change, using a string, when I try the following code, access reports
that it cannot find the form

Forms(frmName).Recordset.RecordCount

But when I reference it directly it works

Form_OldGroupsList.Recordset.RecordCount

but if i use this method does not allow dynamic access
Click to expand...


You need to go through the subform control to get to its
SourceObject. The name of the form object is not used in
this kind of reference

From the main form:
subformcontrol.Form.Recordset.RecordCount

From outside the main form:
Forms(mainform).subformcontrol.Form.Recordset.RecordCount
 
A

amiga1200

Sorry, I posted to forms coding & thought that maybe I should have posted to
programming.

Thanks for the help
 
A

amiga1200

After tinkering with the answer from Wayne I came up with the following code
that does the job

Dim ParentfrmName, ChildfrmName As String
Select Case Screen.ActiveForm.Name
Case "Tracker"
ParentfrmName = "Tracker"
ChildfrmName = "CompaniesList"
Case "Groups"
ParentfrmName = "Groups"
ChildfrmName = "GroupsList"
End Select
Msgbox Forms(ParentfrmName)(ChildfrmName).Form.Recordset.RecordCount

Thanks for the help.
 
M

Marshall Barton

Yes, that's what we were aiming at. However, you are still
dependent on the name of the subform control (on the main
form). In this case, you are dependent on the the subform
control having the same name as the form object it is
displaying.

You could remove most of that by naming the subform control
the same on all the forms (which may be misleading in a
different way). This way the code could be reduced to a
single line that is form name independent:

Screen.ActiveForm.MySuperSubform.Form.Recordset.RecordCount
 
Top