Accessing SubForm Recordset from module

Discussion in 'Access Forms Coding' started by GTajos, Nov 28, 2007.

  1. GTajos

    GTajos Guest

    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
    GTajos, Nov 28, 2007
    #1
    1. Advertising

  2. GTajos wrote:

    >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

    --
    Marsh
    MVP [MS Access]
    Marshall Barton, Nov 28, 2007
    #2
    1. Advertising

  3. GTajos

    GTajos Guest

    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
    GTajos, Nov 28, 2007
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Albert
    Replies:
    11
    Views:
    93
    alberty
    Jul 31, 2005
  2. Pat
    Replies:
    15
    Views:
    74
  3. JR
    Replies:
    3
    Views:
    66
  4. dbqph
    Replies:
    2
    Views:
    139
    dbqph
    Feb 3, 2010
  5. tag
    Replies:
    0
    Views:
    168
Loading...

Share This Page