missing operator in vba

[shutzy]

can someone please help me with this

strSql = "UPDATE tblItems " & _
 "SET StockQTY = ([StockQTY]+1) " & _
 "WHERE [ItemsID] = " & Me.lstCurrentRetail.Column(6) & ""
 CurrentDb.Execute strSql, dbFailOnError

apparently it has a missing operator. im guessing it is because it is using a list box for the where condition.

thanks

 

[JANR]

If lstCurrentRetail.Column(6) is a number:

strSql = "UPDATE tblItems " & _
 "SET StockQTY = ([StockQTY]+1) " & _
 "WHERE [ItemsID] = " & Me.lstCurrentRetail.Column(6)  
CurrentDb.Execute strSql, dbFailOnError

If it is text:

strSql = "UPDATE tblItems " & _
 "SET StockQTY = ([StockQTY]+1) " & _
 "WHERE [ItemsID] = '" & Me.lstCurrentRetail.Column(6) & "'"
 CurrentDb.Execute strSql, dbFailOnError

JR

 

[shutzy]

it is a number but it is still telleing me that there is a missing operator. i tried the text version but that was not correct for this instance.

 

[JANR]

What is the column Count property of the listbox, is it 7 or more??

JR

 

[shutzy]

there is 7 columns, but your reply got me thinking. its in the data source but the columns were limited to 5. ive changed it to 7 and it now works. i didnt know it had to be in the actual list box for it to get the id.

thans for making me think about it

 

[JANR]

You don't actually have to "see" it in the listbox/combobox, but you have to match the columncount with the number of columns you have defined in the rowsource SQL.

You can hide certain columns by setting the column withs to zero.

ex:

ColumnCount: 4
Column Withs: 0;2cm;0;2cm

Here column 2 and 4 are visible and the other two are hidden from view.

JR