Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-02-11
3
Medium Priority
?
115 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 2000 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

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.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

704 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