Multi List Box help

[Guest]

Hello, I am having so much trouble with the multi select list box on a form.
I want to be able to select a few names from the list box and have it run a
query which brings up a report. I just cant figure out the code for the list
box. The list box name is "list1" and my report name is
"rpt_EmployeeComparison" and my query for the report is
qry_EmployeeComparison if that matters. Here is the code I have in the button
for the list box as of now and it works but its bringing up all the names in
the list box because I dont have the code for the multiselct. What and where
in the code I have as of now would I add? Thanks!!



Private Sub cmdEmployeeComparison_Click()
On Error GoTo Err_cmdEmployeeComparison_Click

Dim stDocName As String
Dim stLinkCriteria As String

If ValidDates() Then
If Me.list1 & "" <> "" Then
stDocName = "rpt_EmployeeComparison"
DoCmd.OpenReport stDocName, acPreview
Else
MsgBox "Select Employee for Individual Reports.", vbOKOnly,
"Employee Not Selected"
End If
End If

Exit_cmdEmployeeComparison_Click:
Exit Sub

Err_cmdEmployeeComparison_Click:
MsgBox Err.Description
Resume Exit_cmdEmployeeComparison_Click
End Sub

 

[Guest]

I feel your pain. It took me a while to work this out because I had to work
with 7 list boxes the first time I had to do this.
The basic concept is you need to read through the ItemsSelected collection
of the list box and build a string you can use as the Where argument of the
OpenReport method.
Here is a function that will do that. You just pass the control to it and
it read through the list and builds part of the string:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Here is a modified version of your code (untested) that you can try. You
will have to change SOME_FIELD to the actual name of the field you are
filtering on.

Private Sub cmdEmployeeComparison_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim strWhere As String

On Error GoTo Err_cmdEmployeeComparison_Click

If ValidDates() Then
strWhere = BuildWhere(Me.list1)
If Len(strWhere) > 0 Then
strWhere = "Where SOME_FIELD " & strWhere
stDocName = "rpt_EmployeeComparison"
DoCmd.OpenReport stDocName, acPreview, ,strWhere
Else
MsgBox "Select Employee for Individual Reports.", vbOKOnly,
"Employee Not Selected"
End If
End If

Exit_cmdEmployeeComparison_Click:
Exit Sub

Err_cmdEmployeeComparison_Click:
MsgBox Err.Description
Resume Exit_cmdEmployeeComparison_Click
End Sub

 

[Guest]

Thank you for the reply! I tried the code you added to what I had already and
im getting an error (Compile error: Sub of Function not defined) and its
highlighting this word in the code (BuildWhere). Im totaly lost.... Thanks!

 

[Guest]

Make it a Public function in a standard module or put it in the form's module.

 

[Guest]

Im sorry but im not quite sure what you mean? Thanks!

 

[Guest]

A standard module is a VBA code module you create. In the main database
window, select Modules and click on new. The VBA editor will open with a
blank module. Paste the code in there. Save the module. Do not name the
module the same name as the function.