Multi Select listbox

[s_wadhwa]

Hi,
Can anyone help me in using the multi-selected values of listbox in a
query to database.

any suggestions are welcome.
Thanks,
Shalini

 

[Nikos Yannacopoulos]

Shalini,

If your choice of newsgroup suggests you are hoping to do this with a
macro, I'm afraid I'll disappoint you; multi-select listboxes are a
great component to use in a user interface, in terms of usability, but
there's a price to be paid: they can only be handled through code. That
said, there are several ways you could use it (populate a temp table,
filter on the fly etc.) and the best choice depends on what you want to
do (filter a form or report? export data? ....?). Can you be more specific?

HTH,
Nikos

 

[s_wadhwa]

Hi Nikos,

I'm using multi select listbox in Access Form (user interface). but I
don't know how to handle it through code. Please give any reference
code to use it.
Actually I want to use those multi selected values into a query to
populate recordset and show that recordset values in a subform.

I hope this clarifies my question.

thanks,
Shalini

 

[Nikos Yannacopoulos]

Actually I want to use those multi selected values into a query to
populate recordset and show that recordset values in a subform.

This is not very clear... do you mean you want to use the selected
values in order to filter the subform's recordsource? For instance,
select customers from a customers table, filtering on state as per
listbox selections? If that's the case, then your code would look
something like:

Dim i as Integer
Dim strSel As String
Dim strRS As String
If Me.MyListBox.ItemsSelected.Count = 0 Then Exit Sub
For i = 0 To Me.MyListBox.ListCount - 1
If Me.MyListBox.Selected(i) = True Then
strSel = strSel & Me.MyListBox.ItemData(i) & ", "
End If
Next
If Len(strSel) > 2 Then strSel = Left(strSel, Len(strSel) - 2)
strRS = "SELECT * FROM tblCustomers WHERE State = '" & strSel & "'"
Me.MySubform.Form.Recordsource = strRS

I hope my object name examples are self-explanatory. This code assumes
it is fired by means of a command button on the same form, sort of
"Update List" or something... alternatively, you could fire it on the
Click event of the listbox, in which case updating happens on the fly as
you select/deselect each listbox entry; in that case, comment out or
delete the line:
If Me.MyListBox.ItemsSelected.Count = 0 Then Exit Sub
Updating on the fly looks better, but might be undesired in case of long
lists recordsets, time to refresh.

HTH,
Nikos