By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,965 Members | 2,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,965 IT Pros & Developers. It's quick & easy.

Refer to a form control as a variable in a loop

P: 1
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.
Expand|Select|Wrap|Line Numbers
  1. sqlstr="insert into tblgames (score) values (me.pbScore1);"
  2. doCmd.runsql(sqlstr)
  3. "insert into tblgames (score) values (me.pbScore2);"
  4. doCmd.runsql(sqlstr)
  5. 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
Expand|Select|Wrap|Line Numbers
  1. for i= 1 to .. then
  2.   sqlstr="insert into tblgames (score) values (me.pbScore " & i & ");"
is there a way to do this without retyping the query everytime?
Sep 6 '08 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. for I = 1 to N
  2.  SQLStr = "INSERT INTO tblgames (score) VALUES (" & me.controls("pbscore" & I) & ")"
  3.  DoCmd.RunSQL SQLStr
  4. next I
-Stewart
Sep 6 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.