CODEWITHSUNDEEP
ms-accessvba
ahjohnston25
ahjohnston25

Reputation: 1975

Iterate Through Recordset in Access 2010 VBA

I have to iterate through a table and test 14 different fields for a certain value. All of these fields have a similar name, e.g. foo 1, foo 2, foo 3 etc.

I want to use a For loop to iterate through the different fields so I can condense 15 If-Else If statements to something like the following

For i = 2 to 15
    Dim fieldname as String
    fieldname = "foo " + i
    If conditionMet(MyTableRecordSet![fieldname]) Then
         'Update another column
    End If
Next i

But I know if I do it this way VBA will look for a field literally named "fieldname", it won't interpolate the variable's value as I wish. Is there a way to get the variable's value in there? If not, is there another way to systematically go through a record's values without too many If-Else If statements?

Upvotes: 0

Views: 238

Answers (1)

Santosh
Santosh

Reputation: 12353

Try below code 

For i = 2 To 15
    Dim fieldname As String
    fieldname = "foo " & i
    If conditionMet(MyTableRecordSet(fieldname)) Then
         'Update another column
    End If
Next i

Upvotes: 1

Related Questions