I want to loop through fieldnames 1

[checkOut]

Hi all,

I have a little question
I have to make a few insert queries based on values
the values are stored in fields with name TempCount1, TempCount2, TempCount3 and so on
Now I'll try something like:
While Not rst.EOF
strSQL = "INSERT INTO tblDetails([subLocRef],[artRef],[aantal],[verk_prijs])" & _
" VALUES(" & subKey & "," & rst.Fields("artikelRef&quot & "," & TempCount & rst.Fields("ID&quot & _
",'" & rst.Fields("VVP&quot & "')"
DoCmd.RunSQL strSQL
rst.MoveNext
Wend
THe ID field in the rst correnspond with numbers 1,2,3, and so on.
The problem: The compiler asked for an unknown variable TempCount.
This is true, I need the public stored variable, tempcount1, and so on. How can I handle this problem?

thnx in advance,
Gerard

 

[Shortie]

In you SQL String replace the code

TempCount & rst.Fields("ID&quot

with

iif(rst.Fields("ID&quot=1,TempCount1,iif(rst.Fields("ID&quot=2,TempCount2,iif(rst.Fields("ID&quot=3,TempCount3,0)))

Basically, If ID is 1,2, or 3 the relvant TempCount varaible is used. Otherwise, the value returned is 0. I have assumed that the IF field is a number/numeric field. If I'm wrong, you will need to surrounf the respective numbers with quotes (eg ...."ID&quot="1",TempC.....)

Anyway, I'm sure you can see what I've done. This works well for just a few variables, but if you have TempCount1 to Tempcount20 it won't be so pretty.

 

[KenReay]

Hi

Should it be:

While Not rst.EOF
strSQL = "INSERT INTO tblDetails([subLocRef],[artRef],[aantal],[verk_prijs])" & _
" VALUES(" & subKey & "," & rst.Fields("artikelRef&quot & ",TempCount" & rst.Fields("ID&quot & _
",'" & rst.Fields("VVP&quot & "')"
DoCmd.RunSQL strSQL
rst.MoveNext
Wend
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[checkOut]

Thnx KenReay,

i finish it before u answer my question, but as usual ur sharp-eyed and secure. So u deserve ur star.

hope to use ur (and others) knowledge more in the future,

Gerard