Loop through subform records

Posted on 2006-05-19
Last Modified: 2008-03-17
Hi - I have a main form with a subform set to datasheet view.

I would like to be able to click a command button on the main form that would loop through the subform datasheet and fire the on-focus code of one of the combo boxes for all of the displayed records.  Can this be done?
Question by:snyperj
    1 Comment
    LVL 4

    Accepted Solution

    You do have a couple options.  First move the code that gets fired by your combo box to a function.

    Private Sub cboMyCombo_AfterUpdate()
    'Here's your code you need to move
    End Sub

    Change the above to the following:

    Private Sub cboMyCombo_AfterUpdate()
    Call FunctionName
    End Sub

    Function FunctionName
    'Heres the code that was associated with your combo
    End Function


    There are two options to loop through your subforms records.  Both of them use DAO methodology and require a little understanding of recordsets.  I'm leaving those explanations out but giving you some sample code.

    The first method is the most efficient.  It loops through each record that exists on your subform, using a subform recordset clone.

    Dim rst As DAO.Recordset 'Create a variable named rst as a DAO Recordset object
    Set rst = Me.subForm1.Form.RecordsetClone

        rst.MoveFirst 'Set the pointer to the first record
            While Not rst.EOF  'create a loop to analyze each record (EOF means End Of File)
                    'Put your code in here, call your function etc.  Just calling your function might not
                    'work because I don't know what your function actually does
                rst.MoveNext  'move to the next record

    rst.Close  'Close the recordset object
    Set rst = Nothing 'Clear the variable to nothing

    The second method creates a recordset out of your subforms controls.  This can have unexpected results.  One of the most major is that your Exit events will fire in each text box or combo.

    dim rst as dao.recordset
    dim fld as dao.field

        set rst = forms("InvoiceFormName").Controls("SubformControlName").Form.Recordset

        do while not rst.eof
            for each fld in rst.fields  'In this statement you could choose to only loop through certain controls
                debug.print & ":" & fld.value
                'or put your own code here or call your function
            next fld

        set fld = nothing
        set rst = nothing

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now