Saturday, September 06, 2008

Run an Access report with a filter built from list box values

So let's say you have a report called "All_Crosstab_2_NoID_LEVEL_Reorder" and you want to run the report using a filter with variables defined by a list box called "lstPlayers".

Here's the code behind a button called "cmdReport1" on a form with the list box. The filter will not exist if no list box items are chosen, will be "Player_ID = 'first selected listbox value'" if only one value is chosen, and will be "Player_ID = 'first selected listbox value' OR Player_ID = 'second selected listbox value' OR ..." if more than one are selected.


Private Sub cmdReport1_Click()

stDocName = "All_Crosstab_2_NoID_LEVEL_Reorder"

'### define basis of query
strPreWhere = "Player_ID = "

bolFirstSelected = False

For iCount = 0 To lstPlayers.ListCount - 1

If lstPlayers.Selected(iCount) Then

'### build report filter for 0 (no strWhere),
'### 1 (bolFirstSelected = False)
'### or more (bolFirstSelected = True) listbox selections
If bolFirstSelected = True Then

strWhere = strWhere & " OR " & strPreWhere & lstPlayers.Column(2, iCount)


strWhere = strPreWhere & lstPlayers.Column(2, iCount)
bolFirstSelected = True

End If

End If

Next iCount

DoCmd.OpenReport stDocName, acViewReport, , strWhere

End Sub

Nothing really new to see here, but I thought someone might appreciate seeing the whole thing spelled out.

No comments:

Post a Comment