Solved "Show All" in unbound combobox (1 Viewer)

[tihmir]

Hi all,
I need some help, please.
I have list with names in the combobox and a search button. When I press the search button it shows me a report of all the names with this code:

Private Sub cmd_SearchClick()

    'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    'DO set the values in the next 3 lines.
    strReport = "rpt_dailyPlan"      'Put your report name in these quotes.
    strDateField = "[Date]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewReport     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.

    If IsDate(Me.txtFrom) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtFrom, strcJetDate) & ")"
    End If
    
    If IsDate(Me.txtTo) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtTo + 1, strcJetDate) & ")"
    End If
          
        
    If Trim(Me.cbo_Name & "") <> "" Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[Name] = '" & Me.cbo_Name & "'"
    End If
    
    ' check if the strWhere has some value
    If Trim(strWhere) = "" Then strWhere = "(1=1)"
        
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    
    DoCmd.OpenReport "rpt_dailyPlan", acViewReport
    Resume Exit_Handler

End Sub

How to add "All" in the combobox list and when I choose "All" to be able to create report for all names?
Thank you all.

 

[arnelgp]

what is the Rowsource of your combo?
you can use Union on rowsource of combo:

select [Name] from yourTable UNION select Top 1 "[All]" From yourTable;

now on your code, you need to adjust:

..
..
If Trim(Me.cbo_Name & "") <> "" And (Me.cbo_Name & "") <> "[All]" Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[Name] = '" & Me.cbo_Name & "'"
End If

 

[tihmir]

what is the Rowsource of your combo?

Hi, arnelgp. Thanks for the reply.
My Rowsource type of combo is Table/Query
Thank you for the advice and help.
I changed my SQL and I added the part of the VBA code and now it works perfectly!