Perfishent
asked on
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,AMOUN T)" & _
" VALUES(" & Trim(rs!loc_no) & ",'" & Trim(rs!job) & "','" & rst!job_cost_category & "'," & amt & ")"
DoCmd.RunSQL strSQL3
rst.MoveNext
Loop
rst.Close
rs.MoveNext
Loop
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,AMOUN
" VALUES(" & Trim(rs!loc_no) & ",'" & Trim(rs!job) & "','" & rst!job_cost_category & "'," & amt & ")"
DoCmd.RunSQL strSQL3
rst.MoveNext
Loop
rst.Close
rs.MoveNext
Loop
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Does this work?
amt = rs.Fields(rst!job_cost_category)
ASKER
cat = rst!job_cost_category
amt = rs.Fields(cat)