Access 2010 For Each on Form.Controls

Question

I am very new to VBA so trying to get my head around the differences with C#. I currently have a Sub to conduct a batch import that only works on a single row/record selected from a datasheet subform.

Private Sub cmdImport_Click()

    Dim strBatchID As String
    On Error GoTo NoBatch
    Me.subClaimsToProcessLookup.Form.Controls.Item("txtBatchID").SetFocus
    strBatchID = Me.subClaimsToProcessLookup.Form.Controls.Item("txtBatchID").Text
    GoTo ContinueImport

NoBatch:
    MsgBox "Please select a row", vbOKOnly + vbQuestion, Me.Caption
    Exit Sub

ContinueImport:
    If Not IsNumeric(strBatchID) Then
        MsgBox "Please select a row", vbOKOnly + vbQuestion, Me.Caption
        Exit Sub
    End If

    Dim lngUserID As Long

'...process continues here...

This will only process whichever row the user has selected...now the user would like to simply process all the rows in the datasheet from one button click.

I am wondering if a For Each would work for this, versus having to use the record navigation (Move Next, etc), which I'm not very familiar with...but For Each is a simple concept for me from C#. As in: Private Sub cmdImport_Click()

Dim strBatchID As String
On Error GoTo NoBatch
' Add loop here to go through each item in the datasheet view and process it. 
For Each Me.subClaimsToProcessLookup.Form.Controls.Item("txtBatchID").Text in Me.subClaimsToProcessLookup.Form.Controls
    Me.subClaimsToProcessLookup.Form.Controls.Item("txtBatchID").SetFocus
    strBatchID = Me.subClaimsToProcessLookup.Form.Controls.Item("txtBatchID").Text
    GoTo ContinueImport
Next

NoBatch: MsgBox "Please select a row", vbOKOnly + vbQuestion, Me.Caption 'Exit Sub Continue For

ContinueImport: If Not IsNumeric(strBatchID) Then MsgBox "Please select a row", vbOKOnly + vbQuestion, Me.Caption 'Exit Sub Continue For End If

Dim lngUserID As Long
....process continues here....

Also, if a For Each will do this properly, have I set up my 'Continue For' flow correctly?

In summary, I am looking for 1) Confirmation that Form.Controls can be used as a collection in For Each; and 2) That I have put in correct flow control (Continue For) for the For Each

Fionnuala Fionnuala · Accepted Answer · 2014-03-06T19:25:57

Perhaps:

Private Sub cmdImport_Click()
   ''The recordset from the subform
   Set rs = Me.subClaimsToProcessLookup.Form.Recordset
   Do While Not rs.EOF
       ''Each record in the subform recordset
       sId = rs!ID
       MsgBox sId ''Just to check
       ''Do stuff with id
       rs.MoveNext
    Loop
End sub

Access 2010 For Each on Form.Controls

1 Answers