Multi List Values

[auujxa2 via AccessMonster.com]

I've been stuck on something for 2 days, and I can't figure it out. The
varItem is giving me a "0" value, and it should be giving me a concantenated
text value. (i.e. 1 - Monroe NC)

I'm trying to update a tables yes/no [Indicator] field, based on multi
selected items in a list box.

Dim strSQL As String
Dim varItem As Variant


For Each varItem In Me.lstActiveStores.ItemsSelected

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [Vendor] LIKE " & Me.lstVendors.Column(0) _
And "[Department] LIKE " & Me.cboDepartments.Column(0) _
And "[Stores] LIKE " & varItem

DoCmd.RunSQL strSQL

Next varItem

 

[Steve Sanford]

It looks like the parameters in the WHERE clause are long integers, so I
changed the "Like" to equals (=)

Try this: (watch for line wrap)


Dim frm As Form
Dim ctl As Control

Dim strSQL As String
Dim varitem

' Change "FORM1" to the name of your form
Set frm = Forms!Form1

Set ctl = frm!lstActiveStores

For Each varitem In ctl.ItemsSelected
'ctl.ItemsSelected
strSQL = "UPDATE [MasterTbl]"
strSQL = strSQL & " SET [Indicator] = FALSE"
strSQL = strSQL & " WHERE [Vendor] = " & Me.lstVendors
strSQL = strSQL & " And [Department] = " & Me.cboDepartments
strSQL = strSQL & " And [Stores] = " & ctl.ItemData(varitem)

CurrentDb.Execute strSQL, dbFailOnError
' DoCmd.RunSQL strSQL

Next varitem



HTH