Multiselect listbox and SQL

[Cassie Pennington]

I am trying to write various items from a multiselect list box to an
SQL statement to update a report, without success. SQL only appears to
accept hard-coded data or control values from a form, not variable
data. Any clues as to how I can write several items to an SQL
statement from a multiselect listbox to update a report?

Thanks in anticipation
Cassie

[Steve Jorgensen]

Use For Each varIndex in <listbox>.ItemsSelected (as I recall, ItemsSelected
is the property name), and get the desired column value from each row using
<listbox>.Columns(<datacolumn>, varIndex). Build a string list of values as
you go through the loop, with items separated by commas.

Now, build your SQL statement, and in your WHERE clause, put " <field> In(" &
<valueliststring> & ") "

On 25 Jul 2004 11:53:42 -0700, ca************@btinternet.com (Cassie
Pennington) wrote:

I am trying to write various items from a multiselect list box to an
SQL statement to update a report, without success. SQL only appears to
accept hard-coded data or control values from a form, not variable
data. Any clues as to how I can write several items to an SQL
statement from a multiselect listbox to update a report?

Thanks in anticipation
Cassie

[Jim Allensworth]

On 25 Jul 2004 11:53:42 -0700, ca************@btinternet.com (Cassie
Pennington) wrote:

I am trying to write various items from a multiselect list box to an
SQL statement to update a report, without success. SQL only appears to
accept hard-coded data or control values from a form, not variable
data. Any clues as to how I can write several items to an SQL
statement from a multiselect listbox to update a report?

Thanks in anticipation
Cassie

What you can do is create a criteria string and store it in a textbox
on the form. In the after update event of the list box Or the values
to the ones previously selected.

Here is a copy of a version that uses a list of sales reps to run a
report. The Record Source of the report uses a query that references
the forms textbox - txtRepFilter. The multi-select list box is named
lstReps. In this example the sales reps can be excluded or included,
set by an option group - frInclExcl. It might give you an idea of how
to proceed.

=====================
Dim lst As ListBox
Dim varItem As Variant
Dim strFilter As String

Me.txtRepFilter = ""

Set lst = Me.lstReps
For Each varItem In lst.ItemsSelected
Select Case Me.frInclExcl
Case 1
'Inclusive so 'or' the filter
strFilter = strFilter & "SalesRepID = " _
& Chr(39) & lst.Column(0, varItem) & Chr(39)
strFilter = strFilter & " Or "
Case 2
'Exclusive so 'And' selections
strFilter = strFilter & "SalesRepID <> " _
& Chr(39) & lst.Column(0, varItem) & Chr(39)
strFilter = strFilter & " And "
End Select
Next varItem
'Now strip the last And/Or
Select Case Me.frInclExcl
Case 1
strFilter = left(strFilter, Len(strFilter) - 4)
Case 2
strFilter = left(strFilter, Len(strFilter) - 5)
End Select

Me.txtRepFilter = strFilter
===================

- Jim