Loop through subform records

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?
Who is Participating?
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.name & ":" & fld.value
            'or put your own code here or call your function
        next fld

    set fld = nothing
    set rst = nothing
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.