Report Based on List Box Selection

[tca_VB]

I need a little help; I think I'm close.

I'm trying to run rpt_MainClientCategory based on Category selection from a list box. I have the report based on qry_ClientMain_Category and in the criteria of Category "=[Forms]![frm_CategoryList]![lstCategory]" I have frm_CategoryList created with a list box lstCategory to multi-select a category.

The error I receive is when I click to run the report, I get

Enter Parameter Value
[Forms]![frm_CategoryList]![lstCategory]

instead of the list box showing up.

Also - to preview the report. Do I have to have that on the frm_CategoryList as a command button or can I add the run report to the switchboard manager?

Thanks.

 

[tca_VB]

Report Button in Form with List Box

I also tried to follow the sample database posted with a list box selection to print a report. I'm missing something...

In red is what I changed out from the sample database

Private Sub btnRunReport_Click()
'Lister 30 Sep 2007, 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

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.lstCategory '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 & " Or qry_Category.Category = "
'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 Or qry_Category.Category = "
'If you have more items selected it will look like this
'"5 Or qry_Category.Category = 2 Or qry_KitchenUtensils.lnItmeID ="
'Now we need to chop off the unnecessary end of the string ("Or qry_Category.Category = ")
If strHolder <> "" Then 'Check to make sure an item was selected.
intStrLength = Len(strHolder) - 24 'We count the full length of the string
strHolder = Left(strHolder, intStrLength) 'And remove the unnecessary bit.
strHolder = " qry_Category.Category = " & strHolder 'We add the required bit of the string to the front of the string
DoCmd.OpenReport "rpt_MainClientCategory", acViewPreview, , strHolder 'And use it in the criteria of the open report meathod
Else 'If nothing was selected, run message.
MsgBox "You should select a Category from the list", vbInformation, "No Item Selected"
Me.lstCategory.SetFocus
End If
ExSub:
Exit Sub
ErrHandler:
MsgBox "Error Number: " & Err.Number & " - " & Err.Description, vbInformation, "Opps: ERROR!"
GoTo ExSub
End Sub

 

[bacarat]

Try this link:
http://www.access-programmers.co.uk/forums/showpost.php?p=633791&postcount=2