Full Version: Preview a report based on an item selected in a list box on a fo
UtterAccess Forums > Microsoft® Access > Access Forms
I am trying to preview/print a report based on criteria that I select from a list box in a form. I placed the criteria below in the underlying query of the report but it is still not printing only the selection that I make in the list box . . . :
Like [Forms]![Sales Reports Criteria]![COBRListBox] & "*"
Basically I want it to print only the information that I select in the list box or if nothing is selected in the list box to print everything . . . it is printing everything every time, even when I make a selection in the list box. Please let me know what I need to do to make this work!!!!
Thank you!! Barb
  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.lstKitchenDraw '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_KitchenUtensils.lnItmeID = "
            '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_KitchenUtensils.lnItmeID = "
    'If you have more items selected it will look like this
    '"5 Or qry_KitchenUtensils.lnItmeID = 2 Or qry_KitchenUtensils.lnItmeID ="
    'Now we need to chop off the unnecessary end of the string ("Or qry_KitchenUtensils.lnItmeID =")
   If strHolder <> "" Then 'Check to make sure an item was selected.
        intStrLength = Len(strHolder) - 34 'We count the full length of the string
        strHolder = Left(strHolder, intStrLength) 'And remove the unnecessary bit.
        strHolder = " qry_KitchenUtensils.lnItmeID = " & strHolder 'We add the required bit of the string to the front of the string
        DoCmd.OpenReport "rptKitchenUtensils", 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 Utensil from the list", vbInformation, "No Item Selected"
    End If
Exit Sub
    MsgBox "Error Number: " & Err.Number & " - " & Err.Description, vbInformation, "Opps: ERROR!"
    GoTo ExSub
End Sub

this is from an example of what you want i downloaded.. i by no means know if this is the best way.. but might get you started
I appreciate your response to my question, but unfortunately this is all greek to me . . . I am a new user of access and I have no idea where to even begin with this sample that you sent me!!!! Please help!!! sad.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.