VBA code to filter a report from a selection in a listbox.

[David Ball]

Hi,
I have a database where a welder can be selected from a listbox on a form. When a command button is pressed a report opens filtered to show only records for that particular welder. The listbox is set up so that only one value can be selected, not multiple values or “All”.
The listbox is called lstWelder and the field in the report’s query is called FindWelder.
This has worked well but due to some changes I have made to the FindWelder field in the query I would like to modify the code so that it works like “contains”. I.e, so that it returns any records where the value in FindWelder “contains” the value selected from the listbox.
For example, if the welder “D Jones” is selected from the listbox and there is a record in the query where FindWelder has “Maint, D Jones 25”, that record would be included in the report.
The code I have:

Private Sub Command30_Click()
On Error GoTo Err_Command30_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSql As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
    Set MyDB = CurrentDb()
    strSql = "SELECT * FROM qrySpoolWeldData"
    'Build the IN string by looping through the listbox
    For i = 0 To lstWelder.ListCount - 1
        If lstWelder.Selected(i) Then
            If lstWelder.Column(0, i) = "All" Then
                flgSelectAll = True
            End If
            strIN = strIN & "'" & lstWelder.Column(0, i) & "',"
        End If
    Next i
    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [[COLOR=#00b050]FindWelder[/COLOR]] in " & _
               "(" & Left(strIN, Len(strIN) - 1) & ")"
    'If "All" was selected in the listbox, don't add the WHERE condition
    If Not flgSelectAll Then
        strSql = strSql & strWhere
    End If
    MyDB.QueryDefs.Delete "qryByWelder"
    Set qdef = MyDB.CreateQueryDef("qryByWelder", strSql)
    'Open the query, built using the IN clause to set the criteria
    DoCmd.OpenReport "rptByWelder", acViewReport
    'Clear listbox selection after running query
    For Each varItem In Me.lstWelder.ItemsSelected
        Me.lstWelder.Selected(varItem) = False
    Next varItem
 
Exit_Command30_Click:
    Exit Sub
Err_Command30_Click:
    If Err.Number = 5 Then
        MsgBox "You must make a selection(s) from the list" _
               , , "Selection Required !"
        Resume Exit_Command30_Click
    Else
        'Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_Command30_Click
    End If
 
End Sub

How would the code need to be modified to do this?
Thanks very much
Dave

 

[June7]

Why using QueryDefs? Just apply filter to report when it opens by setting WHERE CONDITION argument of OpenReport.

If this is really a single select listbox, there is no need for looping code and no need for IN. Most of that code can be discarded.

DoCmd.OpenReport "rptByWelder", acViewReport, , "[FindWelder] LIKE '*" & Me.lstWelder & "*'"

 

[David Ball]

Thanks June7,

It is much simpler. However, I can’t get it working correctly. The only time it opens the report with any records shown is when I select welder “RG”. For all other welders it opens an empty report. I’m not sure why it likes RG, although I did notice that RG is the value for FindWelder in the first record of the query?
Also, it does not return records where “RG” has other text with it in the FindWelder field, eg, “Maint RG”.
Thanks

 

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[David Ball]

Thanks, please see attached database.

 

[June7]

The query object has been saved with static filter criteria In("RG") which is why my suggestion doesn't work.

Why use a query of a query as the report RecordSource? qrySpoolWeldData can be the report RecordSource.

 

[David Ball]

Thanks very much, works great.