Private Sub cmdOpenReport_Click()
'Lister 07 Aug 2008, Waipawa New Zealand
'Loops through the values in the list box to produce a
'string criteria to filter a report.
'The loop is the important bit. You could mod' the code
'to add values to a table or combo box etc
'I have changed the code in this example so its simpler to use.
'I have scraped the "Or" statment and replaced it with a "in("value","value","value")" statment, which is a lot cleaner.
'So you have a choice of either now.
On Error GoTo ErrHandler
'Set variables
Dim ctlSource As Control
Dim intCurrentRow, intStrLength As Integer
Dim strHolder As String
Dim vVal As Variant
Set ctlSource = Me.List2 'set control source to look at the list box
For intCurrentRow = 0 To ctlSource.ListCount - 1 'Loop until the end of all the items in the list box
If ctlSource.Selected(intCurrentRow) Then 'If item selected in list box, step into the if statment
vVal = ctlSource.Column(0, intCurrentRow) 'set vVal to the bound value in the list box for this selected item
End If
If vVal <> Empty Then 'If vVal is not "Empty" (it has a value) step into this if statment.
strHolder = strHolder & vVal & ","
'Add vVal and it's needed extra string (",") to it's self.
vVal = Empty 'reset vVal to Empty
End If
Next intCurrentRow 'Loop to next itme in the list
'If only one item is selected your string will look something like
'"5,"
'If you have more items selected it will look like this
'"5,2,"
'Now we need to chop off the unnecessary end of the string (",")
If strHolder <> "" Then 'Check to make sure an item was selected.
intStrLength = Len(strHolder) - 1 'We count the full length of the string
strHolder = Left(strHolder, intStrLength) 'And remove the unnecessary bit.
strHolder = " QCase.CaseID in (" & strHolder & ")" 'We add the required bit of the string to the front of the string
DoCmd.OpenReport "Case Report", acViewPreview, , strHolder 'And use it in the criteria of the open report method
Else 'If nothing was selected, run message.
MsgBox "You should select a Utensil from the list", vbInformation, "No Item Selected"
Me.List2.SetFocus
End If
ExSub:
Exit Sub
ErrHandler:
MsgBox "Error Number: " & Err.Number & " - " & Err.Description, vbInformation, "Opps: ERROR!"
GoTo ExSub
End Sub
