Two multi-select list boxes

[This Guy]

I have been using multi-select list boxes to pass multiple parameters to a
query and then open the corresponding report. This works fine with one list
box. Now I need to have two multi-select list boxes pass multiple parameters
to my query and I am having trouble. How can I modify the code to look for
both list boxes and pass the parameters? I am using Allen Browne's code for
passing parameters to a single list box as follows:

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report 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 = "rptMSAContractReport"

'Loop through the ItemsSelected in the list box.
With Me.lstPGAStatusID
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 = "[PGAStatusID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Insurance Types: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

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

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

Help is greatly appreciated.

 

[This Guy]

Nevermind, I figured that one out. Now I would like another command button
to just run and displaly the query with the same parameters in datasheet view
instead of the report. How can I accomplish this?

Again thanks for any help.

 

[Piet Linden]

I have been using multi-select list boxes to pass multiple parameters to a
query and then open the corresponding report.  This works fine with onelist
box.  Now I need to have two multi-select list boxes pass multiple parameters
to my query and I am having trouble.  How can I modify the code to lookfor
both list boxes and pass the parameters?  I am using Allen Browne's code for
passing parameters to a single list box as follows:

If you use a function to create the filter, then you can just call it
twice. then just do something like
strWhere1 = fCreateFilter(me.lbx1, """")
strWhere2 = fCreateFilter(me.lbxDates, "#")

strFilter = strWhere1 & " AND " & strWhere2
then you can pass strFilter in your open event of your report.

 

[This Guy]

Thank you Piet for your comment.

What I would like to add now is another button to export the query behind
that report to Excel, or at least run it so it can then be exported to excel.
I am using 2007 so it will not export the report and the user needs to
manipulate the data after the fact. What is the best way to do this? My
query is called qryMSAContractReport, with the same list boxes as paramenters
lstPGAStatus and lstMSAStatus.

Thanks.

Nevermind, I figured that one out. Now I would like another command button
to just run and displaly the query with the same parameters in datasheet view
instead of the report. How can I accomplish this?

Again thanks for any help.

I have been using multi-select list boxes to pass multiple parameters to a
query and then open the corresponding report. This works fine with one list
box. Now I need to have two multi-select list boxes pass multiple parameters
to my query and I am having trouble. How can I modify the code to look for
both list boxes and pass the parameters? I am using Allen Browne's code for
passing parameters to a single list box as follows:

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report 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 = "rptMSAContractReport"

'Loop through the ItemsSelected in the list box.
With Me.lstPGAStatusID
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 = "[PGAStatusID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Insurance Types: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

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

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

Help is greatly appreciated.