List box

[Guest]

I have setup a list box with the ability for a user to select multiple items
in the list. Can I create a button that will update my tbl_parameters with
the selections that the user made?

If not, what is the best way of the selections then being used as criteria
in a query?

 

[Stuart McCall]

I have setup a list box with the ability for a user to select multiple
items
in the list. Can I create a button that will update my tbl_parameters with
the selections that the user made?

If not, what is the best way of the selections then being used as criteria
in a query?

Loop over the ItensSelected collection, concatenating each selected item
into a string, separated by commas (if textual data, enclose each one in
quotes), then use the string to build a criteria, like:

strCrit = "FieldName In (" & MyString & ")"

Append this to your query's Select portion and you're good to go.

 

[Guest]

So if I created a button below the list that says 'Select' when a person
clicks this I tie the expression to OnClick which would be?

I dont think I know how to make it do what you suggested! Rookie here.

Any suggestions as to where I can learn more about this?

 

[Stuart McCall]

So if I created a button below the list that says 'Select' when a person
clicks this I tie the expression to OnClick which would be?

I dont think I know how to make it do what you suggested! Rookie here.

Any suggestions as to where I can learn more about this?

You don't need a select button, the list box will allow the user to select
multiple items on its own. Just set the listbox's MultiSelect property to
either simple (click each item to select) or extended (select a la windows
explorer).

Then write some code to gather the selected items into a string. This is
more likely where you'll need your button, to start this process.

Dim strItems As String, itm As Variant

With MyListBox
For Each itm In .ItemsSelected
strItems = strItems & itm & ","
Next
End With
strItems = Left(strItems, Len(strItems) - 1)

That code builds the bulk of your criteria string in strCrit. When the
For..Next loop terminates you have one surplus comma at the end of the
string. The last line of code strips this off. If your selected items are
text, then replace the line between For and Next with:

strItems = strItems & "'" & itm & "',"

To build your criteria string, use:

Dim strCrit As String

strCrit = " WHERE FieldName In (" & strItems & ")"


Ok, that's your criteria built. Are you familiar with "using a criteria in a
query" (your terminology from a previous post) ? If not, post back and we'll
take it from there. Post the SQL of your existing query to make it easier to
add this criteria.