Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

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

    set fld = nothing
    set rst = nothing

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now