How to evaluate text of a Field Name in Access VBA

Question

I'm looking to iterate through a set of fields as part of a table that follow a specific format. The table contains N pairs of these fields (Var_1, Value_1, Var_2, Value_2, etc).

I need to iterate through these fields and extract the data to write it out to a text file. I'd like to use a loop, I'd rather not hardcode each field into my VBA as the fields will likely grow in time and not every field will be populated for every record. I guess if there was a way to "evaluate" a field when its name is dynamically created in a string, that would solve my problem.

So, for example...

For i = 1 to FieldCount
' Write data to each field

Initially I thought of something like this

For i=1 to FieldCount
    myStr = "!Var_"+CStr(i) + " = " + "!Value_" + CStr(i)
WriteLine(myStr)

but course, it prints the text literally... any way to get Access to evaluate that text first?

Perhaps there is a better way to approach this entirely?

This may not be within your control, but the data structure looks very, very bad. — David-W-Fenton
– David-W-Fenton, Commented Apr 6, 2011 at 21:56

HansUp · Accepted Answer · 2011-04-04 22:18:15Z

2

You can use your record set's Fields collection, and refer to the value of any individual field by referencing the field's name.

rs.Fields("field_name").Value

You can generally drop the explicit .Value since that's the field's default property.

Public Sub printFieldPairs()
    Dim strSql As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim N As Long
    strSql = "SELECT * FROM tblJoeS;"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSql)
    With rs
        Do While Not .EOF
            For N = 1 To 3
                Debug.Print .Fields("Var_" & N) & " = " & _
                    .Fields("Value_" & N)
            Next N
            .MoveNext
        Loop
    End With
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

That's what I think you're asking for. However, if the number of field pairs grows, you will have to revise the table structure. So my inclination would be to use a different table structure.

row_identifier fKey  fValue
1              Var_1 foo
1              Var_2 bar
1              Var_3 baz
2              Var_1 abcd

answered Apr 4, 2011 at 22:18

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

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

1 Comment

Joe S Over a year ago

Wow, my first question on here and it was answered very well. Thanks so much HansUp. I can get away with the first implementation for my application, but I like your suggestion for a different table structure. Thanks!

Patrick Honorez · Accepted Answer · 2011-04-05 06:44:58Z

0

As suggested improvement to HansUp answer:

....
Dim fld as DAO.Field
For Each fld in rs.fields
    debug.? fld.value
Next fld

answered Apr 5, 2011 at 6:44

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Collectives™ on Stack Overflow

How to evaluate text of a Field Name in Access VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related