• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

How do I use a variable for the result set field name in Microsoft Access VBA?

I want to use a variable to designate the field name. I need to do something like this:

 amt = rs![rst!job_cost_category]

Here is the rest of the code:  

Do While Not rs.EOF
        strSQL2 = "SELECT job_cost_category FROM job_cost_mapping"
        Set rst = db.OpenRecordset(strSQL2)
        Do While Not rst.EOF
            amt = rs![rst!job_cost_category]
            strSQL3 = "INSERT INTO tJobsAmtsAccts (LOC_NO,JOB,CATEGORY,AMOUNT)" & _
            " VALUES(" & Trim(rs!loc_no) & ",'" & Trim(rs!job) & "','" & rst!job_cost_category & "'," & amt & ")"
            DoCmd.RunSQL strSQL3
            rst.MoveNext
        Loop
        rst.Close
        rs.MoveNext
    Loop
0
Perfishent
Asked:
Perfishent
  • 2
1 Solution
 
aikimarkCommented:
Try this
amt = rs.Fields([rst!job_cost_category])

Open in new window

0
 
PerfishentAuthor Commented:
You put me on the right track, but I got an error when I set rst!job_cost_category as the Fields() parameter. Instead, I had to put the job_cost_category into a variable as follows:
 
cat = rst!job_cost_category
amt = rs.Fields(cat)
0
 
aikimarkCommented:
Does this work?
amt = rs.Fields(rst!job_cost_category)

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now