For Loop SQL Update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thank you in advance for the help.
Here's my situation. I would like to update a a table based on the user
input on a form. A user would select the ListNumber from a dropdown "cboList"
and the range of boxes "cboBoxNumberFrom" and "cboBoxNumberTo". A user will
also type the the beginning LocationNumber "txtLocation". What I want to do
is to insert the locationNumber incrementing it by one as it moves to the
next record. This is what I have and it's not working properly. Please
help. Thanks!

Counter = me.txtLocation
For intBoxNumber = Me.cboBoxNumberFrom To Me.cboBoxNumberTo
SQL = (" Update tblBox " & _
"SET LocationNumber = ' " & Counter & " ' " & _
"Where tblBox.ListNumber = Forms.frmLocationNumber.cboListNumber
")
Counter = Counter + 1

DoCmd.RunSQL SQL

Next intBoxNumber
 
Aleah,

The form reference in your SQL-statement won't work. Replace it with a
variable

First set the variable like:

Dim iLstNr as Integer (assuming this is a numerical value)
iLstNr=Forms.frmLocationNumber.cboListNumber

In your SQL statement replace the form reference in your where statement for
the variable iLstNr.

Maurice
 
Thanks Maurice for the tip but when I replaced it as you suggested, I get an
overflow error. My code actually works but instead of incrementing it on
subsequent records, it increments the whole batch having the appropriate
ListNumber. In otherwords if I need to update 5 rows with values from 3 to
7, the values inserted should be, 3,4,5...7. Instead, all the record have 3
on the first loop, 4 on the 2nd ... and 7 on the last.
I want to do this but its doing this
BoxNumber LocationNumber BoxNumber LocationNumber
1 3 1 7
2 4 2 7
3 5 3 7
4 6 4 7
5 7 3 7

I've since modified it to look like the following but I get prompted to
enter a parameter value for intBoxNumber. Thanks for your help.

Counter = Me.cboLocationNumber
For intBoxNumber = Me.cboBoxNumberFrom To Me.cboBoxNumberTo
SQL = "UPDATE tblBox " & _
"SET LocationNumber = ' " & Counter & " ' " & _
"Where tblBox.ListNumber = Forms.frmInsertLocationNumber.cboListNumber
" & _
" And tblBox.BoxNumber = intBoxNumber "
Counter = Counter + 1
DoCmd.RunSQL SQL
Next intBoxNumber
 
Never mind I found the error. I had to do this " & intBoxNumber &" for it to
work. Thanks!
 
Back
Top