Update Query Using Multi List Box

  • Thread starter auujxa2 via AccessMonster.com
  • Start date
A

auujxa2 via AccessMonster.com

I'm sorry for the several posts, but I figured I'd enter a final detail
thread so we don't have to go back and forth. I understand the concept, but
I'm having issues still. This is what I want to do. (please excuse the
naming conventions, I know thy're poor)

I have a combo box. (Combo64), which lists departments for my company.
I have a List box (List22), which only displays Vendors for the selected
"Department" in (Combo64).

I have a list box (List7), which based on the Department (Combo64) and Vendor
(List22) selected, pulls active stores from (MasterTbl).

What I'd like to do, is be able to multi select (currently does), the active
stores, and based on the stores selected (List7), run an update query that
changes the values in the [MasterTbl] field [Indicator] from -1 to 0...
(yes/no) field. Changing the [Indicator] field from -1 to 0 will uncheck the
box, and make the store inactive.

I know I need to use the For Each... Itemsselected method. But I get a
little lost on how to incorporate it into the query. I'm new to SQL so the
qry defs confuse me at this point.

Any support you can lend will be greatly appreciated. Thank You.
 
A

auujxa2 via AccessMonster.com

This is what I have so far, but it doesn't like the varitem part in the last
line of the SQL statement:

Dim strSQL As String
Dim varitem As Variant


For Each varitem In Me.List7.ItemsSelected

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

DoCmd.RunSQL strSQL

Next varitem
I'm sorry for the several posts, but I figured I'd enter a final detail
thread so we don't have to go back and forth. I understand the concept, but
I'm having issues still. This is what I want to do. (please excuse the
naming conventions, I know thy're poor)

I have a combo box. (Combo64), which lists departments for my company.
I have a List box (List22), which only displays Vendors for the selected
"Department" in (Combo64).

I have a list box (List7), which based on the Department (Combo64) and Vendor
(List22) selected, pulls active stores from (MasterTbl).

What I'd like to do, is be able to multi select (currently does), the active
stores, and based on the stores selected (List7), run an update query that
changes the values in the [MasterTbl] field [Indicator] from -1 to 0...
(yes/no) field. Changing the [Indicator] field from -1 to 0 will uncheck the
box, and make the store inactive.

I know I need to use the For Each... Itemsselected method. But I get a
little lost on how to incorporate it into the query. I'm new to SQL so the
qry defs confuse me at this point.

Any support you can lend will be greatly appreciated. Thank You.
Click to expand...
 
Top