Allen Browne's MultiSelect ??

[Guest]

Hi,
I am trying to adjust Allen Browne's MultiSelect LisxtBox filter for report
example at http://www.allenbrowne.com/ser-50.html

However, my sitation has more to it then just one multiselect listbox. I
have a date text field, a combo box and two list boxes on an unbound form
that will provide filter parameters for a report that is based on quite a
complex query from diffrent tables.

Tables: Included in that report query is tblPlantingDetails, tblApplication,
tblApplicationDetails, tblOperation, tblProducts.
Relationships: tblPlantingDetails 1:M tblApplications, tblOperations 1:M
tblApplications, tblApplications 1:M tblApplicationDetails, tblProducts 1:M
tblApplicationDetails.

On an unbound form, I have txtApplicationDate, cboOperations, lstPlantings,
lstProducts.

I have two command buttons, one is cmdAppend with is already working and is
inserting all selected items to their respective tables, and from there, they
already become part of the query that the report is based on.
... Then while having the same values selected, I want to use the other
command button to cycle through the two list boxes and display on the report
the ones selected from lstPlantings and lstProducts, which also equal to the
txtAppicationDate and cboOperation values.

Any help on the VBA side to get this one sorted will be more than helplful
as I have only started teaching myself VBA and a newbie too. I hope I have
explained the problem okay.

Thanks

 

[Graham Mandeno]

Answered below in the other thread...

 

[Guest]

Hi Graham,
I know I am running two threads for the same problem that I am working one
but I am using two different approaches. Yours as well as the stuff that I
got off Allen Browne's website. I certain these two may not be any different
to you but its kinda different for me. Sorry bout being everywhere. I just
need that one to be working by end of this week.

Here is the piece of code that is on the onclick event of the my command
button cmdPreview.
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 varProducts As Variant 'Selected Products
Dim varPlantings As Variant 'selected Plantings
Dim cbo As ComboBox
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 = "rptSpray"
Set cbo = Me.cboOperation

If Not IsNull(cbo) Then
strWhere = strWhere & "(OperationName = """ & cbo.Column(1) &
""") AND "
End If

'Loop through the ItemsSelected in the list box.

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


For Each varPlantings In lstPlantings.ItemsSelected
With Me.lstPlantings
If Not IsNull(varPlantings) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varPlantings) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(0, varPlantings) &
""", "
End If
End With
Next varPlantings
Next varProducts
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ProductID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Product: " & Left$(strDescrip, lngLen)
End If
End If
If lngLen > 0 Then
strWhere = "[PlantingDetailsID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "PlantingDetailsID: " & 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

When I click on this, the report does not show anything because I am certain
my IN string statement above is not done correctly but I dont' know how it
should look like.
When I run the report, in the Report.OpenArgs ... I get this:

([PlantingDetailsID] IN ([ProductID] IN ((OperationName = "Spraying
Insecticides") AND 8,164,169,9,164,169,10,164,169)))

But I really want it arranged so that I can get Report.OpenArgs to bes
something like this:

[PlantingDetailsID] IN(164,169) AND [ProductID] IN (8,9,10) AND
OperationName = "Spraying Insecticides"


Can someone modify the above onclick event procedure for me to get that
result, please?

Thanks in advance.