Using Multiple Selection from List Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

Can't find the answer to this, I have created a form with a list box which
allows multiple selections. I want to then open another form which shows info
on all records chosen from the list box. Tried setting the 2nd form criteria
to list box control but did not really think that would work (and it didn't!)
I guess it must have to loop through all choices in some way but can't figure
out how, Not really a VB writer so simple answer if pos would be much
appreciated.

Thanks, Sheila D
 
Yes, you will need to loop through the ItemsSelected collection of the list
box, building up a string to use in the WhereCondition of OpenForm.

It's exactly the same process as for OpenReport. This article gives an
example:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
 
Hi, Sheila.

Requery the 2nd combo box in the AfterUpdate event procedure of the first:

Me!MySecondComboBox.Requery

Hope that helps.
Sprinks
 
Hi Allen

I got this working perfectly in Northwind and modified code slightly to use
a form and my field called CODE (have pasted code below) . I'm not getting
any records returned which I think is probably to do with the string length.
My field is Long Integer and numbers can be up to (currently) 4 digits and
could go into 5 digits. How would I cater for this or am I on the wrong
track? Many thanks for your help.

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the FORM filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "Test List Box"

'Loop through the ItemsSelected in the list box.
With Me.CODE
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "
Code: 
 IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If


'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenForm strDoc, WhereCondition:=strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
 
Allen

Just realised I left the table name as Categories and had my old criteria in
the form - now works perfectly, thanks so much and sorry to ask the question
wothout checking my work properly!

Sheila
 
Hi

Can't find the answer to this, I have created a form with a list box
which allows multiple selections. I want to then open another form
which shows info on all records chosen from the list box. Tried
setting the 2nd form criteria to list box control but did not really
think that would work (and it didn't!) I guess it must have to loop
through all choices in some way but can't figure out how, Not really a
VB writer so simple answer if pos would be much appreciated.

Thanks, Sheila D
Click to expand...

There should really be an easier way to do this as this seems like
something that needs to be done quite often as I have come across it many
times.
You are correct, you have to loop through the entries in the list testing
if their selected property is set. then add those to a string that will
build up. then the easiest thing to do is strip off the initial comma and
space leaving the proper format. then use that string in the where part of
an SQL statement (I use the in(a,b,c,d) ) for mine and it works fine. One
of these days, I will write a function to return the string from a listbox.
Good luck
Steve
 
Back
Top