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
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

Signature
Dave Hargis, Microsoft Access MVP
> 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
[quoted text clipped - 30 lines]
> Resume Exit_cmdEmployeeComparison_Click
> End Sub
Chad - 04 Oct 2007 06:26 GMT
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!

Signature
Newbies need extra loven.........
> 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.
[quoted text clipped - 98 lines]
> > Resume Exit_cmdEmployeeComparison_Click
> > End Sub
Klatuu - 04 Oct 2007 14:51 GMT
Make it a Public function in a standard module or put it in the form's module.

Signature
Dave Hargis, Microsoft Access MVP
> 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
[quoted text clipped - 102 lines]
> > > Resume Exit_cmdEmployeeComparison_Click
> > > End Sub
Chad - 04 Oct 2007 23:59 GMT
Im sorry but im not quite sure what you mean? Thanks!

Signature
Newbies need extra loven.........
> Make it a Public function in a standard module or put it in the form's module.
>
[quoted text clipped - 104 lines]
> > > > Resume Exit_cmdEmployeeComparison_Click
> > > > End Sub
Klatuu - 05 Oct 2007 14:55 GMT
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.

Signature
Dave Hargis, Microsoft Access MVP
> Im sorry but im not quite sure what you mean? Thanks!
>
[quoted text clipped - 106 lines]
> > > > > Resume Exit_cmdEmployeeComparison_Click
> > > > > End Sub