Show All Button on a form is not unfiltering data within a date range

Question

Hello Everyone,

I have a split form called frmPendingQCSearch which was created from on a query called qryPendingQualityControl as shown by the query's SQL: -

SELECT tblSupplyChainOrder.OrderID, tblSupplyChainOrder.Item, tblSupplyChainOrder.OrderDate, tblSupplyChainOrder.[OrderQC'd]
FROM tblSupplyChainOrder
WHERE (((tblSupplyChainOrder.[OrderQC'd])=No));

There are two text fields (called txtQCDateFrom and txtQCDateTo) on the form to filter the OrderDate from the start of the month to the end of the month.
When an user clicks the search button (i.e. cmdSearchQCDate), the data is filtered by month correctly. However, my problem is that when an user clicks the ShowAllButton (i.e. cmdShowAllQCDate), the filtered data is unchanged and the filtering is not removed. It do not show all of the data again. My VBA is shown below: -

Private Sub cmdShowAllQCDate_Click()
Dim strsearch As String
Dim strText As String


strText = ""
On Error Resume Next
strText = Me.txtQCDateFrom.Value Or Me.txtQCDateTo.Value
strsearch = "SELECT tblSupplyChainOrder.OrderID, tblSupplyChainOrder.Item, tblSupplyChainOrder.OrderDate, tblSupplyChainOrder.OrderQC'd FROM tblSupplyChainOrder ;"


Me.RecordSource = strsearch


txtQCDateFrom.Value = ""
txtQCDateTo.Value = ""

End Sub

Another issue is that the Printer Preview button do not show the filtered data after a search between a monthly date range occurs. The VBA code is shown below (I referenced Allen Browne's Method 2: : Form for entering the dates - http://allenbrowne.com/casu-08.html):-

Private Sub cmdQCPreviewReport_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: 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 = "rptPendingQC"      'Put your report name in these quotes.
    strDateField = "[OrderDate]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.

    'Build the filter string.
    If IsDate(Me.txtQCDateFrom) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtQCDateFrom, strcJetDate) & ")"
    End If
    If IsDate(Me.txtQCDateTo) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtQCDateTo + 1, strcJetDate) & ")"
    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.
    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

Thank you in advance for your assistance to solve these two issues.

Answer 1

For the first problem, try removing " 'd " and avoid using On Error Resume Next excessively. Also remove the strText variable.

For the second problem, uncomment Debug.Print to check the value of strWhere.

Answer 2

Hi @Viorel ,

Thank your for your responses. I tried this code for my first problem and it worked for my first issue: -
Dim strsearch As String
strsearch = "SELECT tblSupplyChainOrder.OrderID, tblSupplyChainOrder.Item, tblSupplyChainOrder.OrderDate, tblSupplyChainOrder.[OrderQC'd] FROM tblSupplyChainOrder"
Me.RecordSource = strsearch
txtQCDateFrom.Value = Null
txtQCDateTo.Value = Null

For the second problem, I followed your advice and and uncomment Debug.Print to check the value of strWhere. There was no issue and the Print Preview button filtered the data between the two date ranges.

Thank you for your assistance