Refer to a form control as a variable in a loop

[bowl3969]

I have a form with a lot of controls on it that will be used to update tables. The nature of the data is such that I will be running queries based on the control name.

sqlstr="insert into tblgames (score) values (me.pbScore1);"
doCmd.runsql(sqlstr)
"insert into tblgames (score) values (me.pbScore2);"
doCmd.runsql(sqlstr)
insert into tblgames (score) values (me.pbScore3) etc..

This may be a lack of experience thing but I would like to refer to the form control in a loop sort of like

for i= 1 to .. then
  sqlstr="insert into tblgames (score) values (me.pbScore " & i & ");"

is there a way to do this without retyping the query everytime?

[Stewart Ross]

Yes there is, by adding the number to the name of the control similarly to what you showed in your post and using this full control name to refer to the value of a specific control in the controls collection. It's easier to do than to describe:

for I = 1 to N
 SQLStr = "INSERT INTO tblgames (score) VALUES (" & me.controls("pbscore" & I) & ")"
 DoCmd.RunSQL SQLStr
next I

-Stewart