Results 1 to 9 of 9
  1. #1
    JAPA1972 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Jan 2019
    Posts
    14

    Getting the recordset of a sub form from a module

    I am trying to get the recordset of a subform (SubForm) from a module. This subform is inside a tab (the main form (MainForm) contains a tab control). What am I missing here? Thanks.



    'The last statement of code will not work.
    'The below code is execute from a module
    Dim rs As DAO.Recordset


    DoCmd.OpenForm "frmMainForm", acNormal
    Forms!frmMainForm!frmSubForm.SetFocus 'this works
    Set rs = Forms!frmMainForm!frmSubForm.Recordset.Clone 'this will not work

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If the subform's recordsource is a table or query, why not just set that as the rs?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    ...besides, it's RecordsetClone not Recordset.Clone
    EDIT - after a moment or two of reflection, I'm not sure the reference is correct either. Looks like it refers to MainForm > SubForm and not MainForm>subform control>subform.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    According to Allen Browne post-v2003 versions cannot understand some expressions that work in previous versions...such as RecordSetClone. For example, this expression in the Control Source of a text box results in #Name:

    =[Form].[RecordsetClone].[RecordCount]

    and gives a workaround

    http://allenbrowne.com/RecordCountError.html

    It's a different situation...but perhaps that will help.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I don't recall that bug but it must have been fixed a long time ago...
    This works in A2010 whether or not the subform is empty
    Code:
    Me.txtRecordCount = Me.subFormControlName.Form.RecordsetClone.RecordCount
    Click image for larger version. 

Name:	Capture.PNG 
Views:	7 
Size:	12.4 KB 
ID:	40273
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As biblical as Allen's site is, you have to remember that it's not keeping up with the changes. That being said, it will be a huge loss when it's no longer there. Makes me wonder who's paying to keep it available and for how much longer.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Allen is still paying to keep the site online.
    However, his only involvement with it is maintenance.
    I contacted him a year or so ago when it went down and he made sure the host got it back online pronto.

    Chip Pearson's site is perhaps more of a worry as he died last year

    The Wayback Machine site is a huge repository which keeps historical snapshots of all websites that allow web crawlers
    Its saved me on numerous occasions with my own website
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    He should ask for donations to keep it running. I for one would have no problem with that. After all, Wikki and others ask for donations.
    What happens when he passes on or simply gets tired of paying out of his own pocket to keep it running? I remember how I felt/thought when it went down last year.

    I took a quick look at the link and while it may be valuable to someone who has a site, a paid subscription to find something posted by those knowledgeable people isn't really practical in my mind.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The Wayback Machine is free to use though they accept donations.
    It is useful for finding how pages have changes as well as pages that no longer exist e.g. MS help articles that have since been removed

    My existing website is based on a Flash site builder package which will shortly be discontinued
    As a result, I have to redo the entire website (150+ pages) in a new web builder package by the end of the year.
    For that reason, although it is automatically done periodically by Wayback Machine site, I'm currently archiving the entire site to there myself as its highly unlikely I will get it all redone to the new secure https site in time.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-09-2015, 10:16 AM
  2. Replies: 2
    Last Post: 10-31-2014, 07:42 AM
  3. class module vs regular module
    By Madmax in forum Modules
    Replies: 1
    Last Post: 05-01-2012, 03:44 PM
  4. Replies: 2
    Last Post: 03-08-2012, 12:59 PM
  5. Replies: 4
    Last Post: 05-16-2011, 04:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums