change field name in code

[dcollins]

I have a table that has fields called counter1, counter2, etc up to 6. I need to move the data in one of those fields, depending on some selection the user makes, to a new table. I use a string variable glbCounter to trap which selection the user made and am trying to concatinate the choice with rst!counter so that I will get the variable rst!counter6, for example. I want the value in rst!counter6 to be put in a new table in the field ExportAmount (which is a string). In the code below, tempFieldName = "rst!counter6" but I need it to be rst!counter6 (without quotes).

tempFieldName = "rst!counter" & modStartup.glbCounter
rstNew!ExportAmount = Lpad(tempFieldName, "0", 9)

Am I going about this all wrong?
Thanks.

 

[gemma-the-husky]

i think what you are doing will set the value of field exportamount to a text string, rather than the value of the referenced field - is that is happening

is you want to find the content of the field "counter6"

then you need the syntax

myvalue = rst.fields("counter" & requiredref)

(it might need quotes to get it in a string - try and see what happens)
myvalue = rst.fields("counter" & chr(34) & requiredref & chr(34))

so if requiredref is 6 you end up with

myvalue = rst.fields("counter6")

is that what you want?

----------
set a breakpoint, and trace to see what is going on

 

[dcollins]

Yes! That is exactly what I wanted and it works (I didn't need the chr(34), it worked without). Thanks so much for your help.