Create filters for report based on list box

[kittyfool via AccessMonster.com]

Hi Gurus,

How can i add a filter from a list box to create generate my report based on
the filters(multi selection). I have gone through some of the VBA and dont
quite understand them.

I got a report named GEM_Report. My form name is "Selection". I got a combo
box and list box on my form. How can i get all these into a VBA? to generate
my report based on the filter selected from the list box.,

Help is very much appreciated

 

[Guest]

Here is a function I use for that purpose. It evaluates the ItemsSeleteced
collection of the List Box and returns a string of the selected items. Since
I have 8 List Boxes on the form that uses this, it does not include the name
of the field to compare on. You have supply that after the string has be
created.

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

 

[kittyfool via AccessMonster.com]

Hi Klatuu,

How can i use this function to open my report based on the filter that i
select in the list box?

Here is a function I use for that purpose. It evaluates the ItemsSeleteced
collection of the List Box and returns a string of the selected items. Since
I have 8 List Boxes on the form that uses this, it does not include the name
of the field to compare on. You have supply that after the string has be
created.

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

Hi Gurus,

[quoted text clipped - 7 lines]

Help is very much appreciated

 

[Guest]

It will not open your report for you. It only puts together the items
selected into a format that can be used as the Where argument. It does not
include the field name, either. You will have to put that part together.
Sort of like this:

strWhere = BuildWhereCondition("lstSearch")
If Len(strWhere) > 0 Then
strWhere = "[SomeTableField] " & strWhere
End If

Docmd.OpenReport "MyReport",,,strWhere

Hi Klatuu,

How can i use this function to open my report based on the filter that i
select in the list box?

Here is a function I use for that purpose. It evaluates the ItemsSeleteced
collection of the List Box and returns a string of the selected items. Since
I have 8 List Boxes on the form that uses this, it does not include the name
of the field to compare on. You have supply that after the string has be
created.

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

Hi Gurus,

[quoted text clipped - 7 lines]

Help is very much appreciated