Getting report to include only items selected in listbox

  • Thread starter Thread starter Spidey3721
  • Start date Start date
S

Spidey3721

I am trying to limit a report to only include items that the user has
selected in a multiselect list box. Right now I am setting the criteria of
the listbox control source to the following.

Forms![NameofForm]![NameofListBox].ItemsSelected
 
Spidey3721 said:
I am trying to limit a report to only include items that the user has
selected in a multiselect list box. Right now I am setting the
criteria of the listbox control source to the following.

Forms![NameofForm]![NameofListBox].ItemsSelected
Click to expand...

I presume you mean you're setting the criteria of the report's
recordsource -- but that still won't work. The standard and most
efficient way of doing this is to use code to loop through the list
box's ItemsSelected collection and build up a criteria string, which you
then specify as the WhereCondition argument on the DoCmd.OpenReport
statement that opens the report. Code looks something like this:

'----- start of example code ("air code") -----
Private Sub cmdPreviewReport_Click()

Dim strCriteria As String
Dim varItem As Variant

With Me.lstMyListBox
For Each varItem In .ItemsSelected
strCriteria = strCriteria & ", " & .ItemData(varItem)
Next varItem
End With

If Len(strCriteria) = 0 Then
MsgBox "No items selected for the report!"
Else
' drop leading ", " and add rest of condition
strCriteria = "KeyField In (" & Mid$(strCriteria, 3) & ")"

' display the report
DoCmd.OpenReport "rptMyReport", acPreview, _
WhereCondition:=strCriteria
End If

End Sub
'----- end of example code ("air code") -----
 
Wondering if you could explain exactly what the following line does:

strCriteria = "KeyField In (" & Mid$(strCriteria, 3) & ")"




Dirk Goldgar said:
Spidey3721 said:
I am trying to limit a report to only include items that the user has
selected in a multiselect list box. Right now I am setting the
criteria of the listbox control source to the following.

Forms![NameofForm]![NameofListBox].ItemsSelected
Click to expand...

I presume you mean you're setting the criteria of the report's
recordsource -- but that still won't work. The standard and most
efficient way of doing this is to use code to loop through the list
box's ItemsSelected collection and build up a criteria string, which you
then specify as the WhereCondition argument on the DoCmd.OpenReport
statement that opens the report. Code looks something like this:

'----- start of example code ("air code") -----
Private Sub cmdPreviewReport_Click()

Dim strCriteria As String
Dim varItem As Variant

With Me.lstMyListBox
For Each varItem In .ItemsSelected
strCriteria = strCriteria & ", " & .ItemData(varItem)
Next varItem
End With

If Len(strCriteria) = 0 Then
MsgBox "No items selected for the report!"
Else
' drop leading ", " and add rest of condition
strCriteria = "KeyField In (" & Mid$(strCriteria, 3) & ")"

' display the report
DoCmd.OpenReport "rptMyReport", acPreview, _
WhereCondition:=strCriteria
End If

End Sub
'----- end of example code ("air code") -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Spidey3721 said:
Wondering if you could explain exactly what the following line does:

strCriteria = "KeyField In (" & Mid$(strCriteria, 3) & ")"
Click to expand...

The code has been building a list of values to go inside a SQL "In"
clause, which supports this syntax:

<FieldExpression> In (value1 [, value2 ...])

That is to say, "include records where <FieldExpression> is one of the
values in the list." At the time the code loop finishes, strCriteria
has a value something like this (if there were three selected items):

, ListValue1, ListValue2, ListValue3

The Mid$() function is used to drop the leading ", ", giving just this:

ListValue1, ListValue2, ListValue3

which is then concatenated with the surrounding string literals to give
this final value:

KeyField In (ListValue1, ListValue2, ListValue3)

That is the WhereCondition that is applied to the opening report.

Note that if the field to be filtered on -- "KeyField" in this
example -- is a text field, then the list values have also to be
enclosed in quotes.
 
Back
Top