Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 128
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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