Report Filter with VBA

[Sreedhar]

I've this procedure that attempts to filter a Report based on the values
chosen from a Multi-Select ListBox. But I'm unable to filter the Report
correctly if more than one value is chosen (Report is blanked out). Can
somebody please help me get this right ?

Thanks.

Private Sub cmdFilter_Click()
On Error GoTo Err_cmdFilter_Click

Dim i As Integer
Dim blnSelected As Boolean
Dim strSQL As String

blnSelected = False
For i = 0 To Me.lstBank.ListCount - 1
If Me.lstBank.Selected(i) Then
blnSelected = True
strSQL = strSQL & Me.lstBank.Column(0, i) & " OR "
End If
Next i
If blnSelected = False Then
MsgBox "Please select a row.", vbInformation, "Invalid Selection"
GoTo Exit_cmdFilter_Click
End If

strSQL = Left(strSQL, Len(strSQL) - 4)

Reports!rptCheque.Filter = "BankID = '" & strSQL & "'"
Reports!rptCheque.FilterOn = True


Exit_cmdFilter_Click:
Exit Sub

Err_cmdFilter_Click:
LogError "cmdFilter_Click - " & Me.Name, Err, Error
Resume Exit_cmdFilter_Click

End Sub

 

[Jim Burke in Novi]

you need a loop like the following (with the dim statement at the beginning
of the sub/function):

Dim listItem As Variant

...

For Each listItem In lstReasonCodes.ItemsSelected

' refer to the list item as follows:...

lstReasonCodes.Column(0, listItem)

Next listItem

This is from a list with multiple columns, referencing the value of the
first column (column(0)). If you only have one column I think you'd use
lstReasonCodes.Column(listItem)

 

[Sreedhar]

OK, that is another way of getting the selected values from the ListBox. But
my problem is not addressed - Filter the Report with those values from the
ListBox.
Anything that works ?

 

[Sreedhar]

Hi everybody,

I got my answer here : http://www.mvps.org/access/forms/frm0007.htm

Thanks folks.