Iterate Through Recordset in Access 2010 VBA

Question

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?

Santosh · Accepted Answer · 2013-10-12 02:11:53Z

1

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

edited Oct 12, 2013 at 2:11

answered Oct 12, 2013 at 2:05

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ahjohnston25 Over a year ago

Thank you so much! I rarely program in VBA so I'm unfamiliar with its idiosyncrasies.

Collectives™ on Stack Overflow

Iterate Through Recordset in Access 2010 VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related