CODEWITHSUNDEEP
ms-accessvba
Joe S
Joe S

Reputation: 23

How to evaluate text of a Field Name in Access VBA

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?

Upvotes: 0

Views: 4769

Answers (2)

iDevlop
iDevlop

Reputation: 25262

As suggested improvement to HansUp answer: 

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

Upvotes: 0

HansUp
HansUp

Reputation: 97101

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

Upvotes: 2

Related Questions