Accessing SubForm Recordset from module

[GTajos]

I am trying to access the Recordset of a subform from a module (not with in
the main form nor the subform)

If this has been answered before please forgive me. I did spend 40 minutes
digging for this answer.



Sub debugGetDSrs()
Dim Frm As Form, SubFrm As Form, SubFrmCtrl As Control, RS As Recordset, fld
As Field

Set Frm = Forms("CR_Frm")
Set SubFrmCtrl = Frm.Controls("CR_DS")

Debug.Print SubFrmCtrl.SourceObject 'This returns "CR_DataSheet" as it should

Debug.Print SubFrmCtrl.Form.Name 'This returns "CR_DataSheet" as it should

Debug.Print SubFrmCtrl.Form.Recordset.RecordCount 'This returns "2022" as
it should

Set RS = SubFrmCtrl.Form.Recordset 'This Errors with a type mismatch

For Each fld In RS.Fields
Debug.Print fld.Name
Next

Set RS = Nothing
Set SubFrmCtrl = Nothing
Set Frm = Nothing

End Sub

The Main Form Name is "CR_Frm"
The name of the control that contians the subform is "CR_DS"
The subform name is "CR_DataSheet"

Thanks,
Garry

 

[Marshall Barton]

I am trying to access the Recordset of a subform from a module (not with in
the main form nor the subform)

If this has been answered before please forgive me. I did spend 40 minutes
digging for this answer.


Sub debugGetDSrs()
Dim Frm As Form, SubFrm As Form, SubFrmCtrl As Control, RS As Recordset, fld
As Field

Set Frm = Forms("CR_Frm")
Set SubFrmCtrl = Frm.Controls("CR_DS")

Debug.Print SubFrmCtrl.SourceObject 'This returns "CR_DataSheet" as it should

Debug.Print SubFrmCtrl.Form.Name 'This returns "CR_DataSheet" as it should

Debug.Print SubFrmCtrl.Form.Recordset.RecordCount 'This returns "2022" as
it should

Set RS = SubFrmCtrl.Form.Recordset 'This Errors with a type mismatch

For Each fld In RS.Fields
Debug.Print fld.Name
Next

Set RS = Nothing
Set SubFrmCtrl = Nothing
Set Frm = Nothing

End Sub

The Main Form Name is "CR_Frm"
The name of the control that contians the subform is "CR_DS"
The subform name is "CR_DataSheet"

It sounds like you have your DAO and ADO References mixed
up.

Just guessing now, but maybe you have the ADO reference
above the DAO reference and because you are not
disambiguating your declarations, you are getting the wrong
one.

Take a little time out and think about which references you
really need and if you don't really need both of those,
uncheck the one you can do without.

If you really do require both (or just as a good practice)
change your decalarations to specify which library you
intend to use in each instance. Either
Dim rs As DAO.Recordset
or
Dim rs As ADODB.Recordset

 

[GTajos]

Marshall,

Thank you! the: Dim rs As DAO.Recordset was the fix.

Just a reminder to those of use who forget what the have learned sometimes:
Access uses DAO recordsets for forms

If figured this out but not in time to bet your reply.

Thanks again,
Garry