Clear Filter/Filter selection issue

[vapid2323]

Before I start, I am NOT a programmer, but I can normally Google my way past most issues.

Ok I have two issues; I am working with the code attached below.

I want users to be able to hit a command button and a report will come up either filtered based on the Combo and text boxes on the form or to come up with all results if the filters are empty.

The issue is that my code will not see anything that’s in a text or combo box unless a user clicks out of a box then clicks on the command button to open the report.

My other problem is I can’t for the life of me get the filters to be cleared out if nothing is in the combo boxes!

Attached code: Please note that it’s not clean as I have been trying a few things based on forums etc.

Private Sub btn1_Click()
'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: [URL]http://allenbrowne.com/casu-08.html[/URL]
    '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 strSite As String
    Dim strCOI As String
    Dim strMon As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change
    'DO set the values in the next 3 lines.
    strReport = "rptSitesWithNo" 'Report Name
    strDateField = "[VisitDate]" 'Put your field name in the square brackets in these quotes.
    strSite = "[SiteName]" 'Site Name Field
    strCOI = "[COI]" 'COI Field
    strMon = "[MonitoredBy]" 'MonitoredBy Field
    lngView = acViewPreview 'Use acViewNormal to print instead of preview.
    strWhere = ""
    'Build the Date filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND " 'Checks to see if Is Null, if not adds AND
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If
 
    'Build the Site Name Filter
    If Me.cboSiteName <> vbNullString Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND " 'Checks to see if Is Null, if not adds AND
        End If
        strWhere = strWhere & "[SiteName] =""" & Me.cboSiteName & """ "
    End If
 
    'Build the COI Filter
    If Me.txtCOI <> vbNullString Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND " 'Checks to see if Is Null, if not adds AND
        End If
        strWhere = strWhere & "[COI] =""" & Me.txtCOI & """ "
    End If
 
    'If strWhere = "" Then
        'strWhere = "1=1 "
    'End If
 
    '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.
    'On Error Resume Next
    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
    Resume Exit_Handler
End Sub

And the code I am using if a user wants to clear the cbo/txt boxes.

Private Sub btn2_Click()
Dim cControl As Control
strWhere = "1=1 "
    For Each cControl In Me.Controls
        If cControl.Tag Like "btn2" Then cControl = vbNullString
    Next
End Sub

 

[vapid2323]

Well it looks like my code is fine, it was just a matter of me trying to use a label instead of a command button (I like the way labels look)

Anyone have any idea why a Label would create these issues?

 

[vbaInet]

For your first problem, step through the code to see what the value of the textbox is at the relevant lines.

If you like labels, the you need to specify it's a lablel you want and set the Caption property, not the Value property (because a label doesn't have a Value).

    For Each cControl In Me.Controls
        Select Case cControl.ControlType
             Case acLabel
                  If cControl.Tag Like "btn2" Then cControl.Caption = vbNullString
        End Select
    Next