Solved

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

Posted on 2015-02-11
3
99 Views
Last Modified: 2016-02-10
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
Comment
Question by:Perfishent
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 46

Accepted Solution

by:
aikimark earned 500 total points
ID: 40604216
Try this
amt = rs.Fields([rst!job_cost_category])

Open in new window

0
 

Author Closing Comment

by:Perfishent
ID: 40604500
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40604795
Does this work?
amt = rs.Fields(rst!job_cost_category)

Open in new window

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question