form lists

[auujxa2 via AccessMonster.com]

If I wanted to run an update query based on the multi-selected items in a
list box, how can I capture all the selected records? Normally, I'd enter
the following in the query criteria.

[forms].[frmMain].[lstActiveStores]

But I think selecting multiple records in the list doesn't allow this.

Please help.

Thanks

 

[Jeff Boyce]

If you are saying you want to do the same thing for each item selected,
you'll need to add in a loop that loops through all/each selection, does its
thing, then moves to the next.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

If I wanted to run an update query based on the multi-selected items in a
list box, how can I capture all the selected records? Normally, I'd enter
the following in the query criteria.

[forms].[frmMain].[lstActiveStores]

But I think selecting multiple records in the list doesn't allow this.

Please help.

Thanks

Multiselect listboxes don't have a "value" exposed to the query engine. You'll
need to write VBA code to loop through the listbox's ItemsSelected collection
and build a query SQL string containing a clause like

WHERE StoreID IN(112, 231, 428, 551)

assuming those are the selected values. You can then use this SQL string as
the recordsource for a form or report.

 

[Dale Fye]

Another way to do this, is to add a field (IsSelected) to the table that
contains the records in your listbox. Then, in the Click event of your
listbox, do something like:

Private sub lstActiveStores_Click

Dim strSQL as string

strSQL = "UPDATE tbl_ActiveStores " _
& "SET [IsSelected] = NOT [IsSelected] " _
& "WHERE [ID] = " & me.lst_ActiveStores.column(0)
currentdb.execute strsql

End Sub

Then you could create your query something like:

SELECT *
FROM yourTable
WHERE [IsSelected] = True

Which would be much faster than the amount of time it takes to create the IN
( ) clause and then execute the query. If you use this technique, you
need to keep in mind that you should set all of the IsSelected values to
False when you open the form. When I do this, I generally include the
IsSelected field as a field in the list so that the user can determine which
items are selected (sure wish Access 2003 and below had a checkbox property
for it's lists).

I probably use the technique that John mentioned 60-70% of the time, but
find this method works well on occassion.

HTH
Dale

If I wanted to run an update query based on the multi-selected items in a
list box, how can I capture all the selected records? Normally, I'd enter
the following in the query criteria.

[forms].[frmMain].[lstActiveStores]

But I think selecting multiple records in the list doesn't allow this.

Please help.

Thanks

Multiselect listboxes don't have a "value" exposed to the query engine.
You'll
need to write VBA code to loop through the listbox's ItemsSelected
collection
and build a query SQL string containing a clause like

WHERE StoreID IN(112, 231, 428, 551)

assuming those are the selected values. You can then use this SQL string
as
the recordsource for a form or report.

 

[auujxa2 via AccessMonster.com]

THANK YOU!!

If I wanted to run an update query based on the multi-selected items in a
list box, how can I capture all the selected records? Normally, I'd enter
the following in the query criteria.

[forms].[frmMain].[lstActiveStores]

But I think selecting multiple records in the list doesn't allow this.

Please help.

Thanks

 

[auujxa2 via AccessMonster.com]

I'm getting a type mismatch error: I want to update the check box (indicator)
to 0 in MasterTbl based on the criteria

Dim strSQL As String
Dim varitem As Variant

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

For Each varitem In Me.List7.ItemsSelected

DoCmd.RunSQL strSQL

Next varitem

Another way to do this, is to add a field (IsSelected) to the table that
contains the records in your listbox. Then, in the Click event of your
listbox, do something like:

Private sub lstActiveStores_Click

Dim strSQL as string

strSQL = "UPDATE tbl_ActiveStores " _
& "SET [IsSelected] = NOT [IsSelected] " _
& "WHERE [ID] = " & me.lst_ActiveStores.column(0)
currentdb.execute strsql

End Sub

Then you could create your query something like:

SELECT *
FROM yourTable
WHERE [IsSelected] = True

Which would be much faster than the amount of time it takes to create the IN
( ) clause and then execute the query. If you use this technique, you
need to keep in mind that you should set all of the IsSelected values to
False when you open the form. When I do this, I generally include the
IsSelected field as a field in the list so that the user can determine which
items are selected (sure wish Access 2003 and below had a checkbox property
for it's lists).

I probably use the technique that John mentioned 60-70% of the time, but
find this method works well on occassion.

HTH
Dale

On Mon, 31 Mar 2008 20:48:33 GMT, "auujxa2 via AccessMonster.com"
<u37567@uwe>

[quoted text clipped - 23 lines]

as
the recordsource for a form or report.

 

[John W. Vinson]

I'm getting a type mismatch error: I want to update the check box (indicator)
to 0 in MasterTbl based on the criteria

Dim strSQL As String
Dim varitem As Variant

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

For Each varitem In Me.List7.ItemsSelected

DoCmd.RunSQL strSQL

Next varitem

Try using False rather than No, and do include a blank. If you step through
your code you'll see that strSQL is being set to

UPDATE [MasterTbl]SET [Indicator] = NoWHERE ...

Also you need ONE WHERE clause not three:

Try

strSQL = "UPDATE [MasterTbl] SET [Indicator] = False" _
& " WHERE [MasterTbl].[Vendor] = " & Me.List22.Column(0) _
& " AND [MasterTbl].[Department] = " & Me.Combo64 _
& " AND [MasterTbl].[Stores] = " & Me.List7.Column(0)

If Department or Stores are Text fields you will need the syntactially
required quotemarks.

This will NOT work with a multivalue listbox, though; the Column() property
doesn't have a distinct value.