Clearing data in a form after clicking print report

[dullster]

I have a form that searches all Vendor payments and the subform produces all the payments to that vendor. I have created a report that prints the results by clicking "Print Report" button. I would like to clear the data on the form when I open that report. Attached is the code that opens the report but I can't get it to clear the data.

When I exit that form and reopen it, It still has my last search information in the subform. I've tried "on close" me.requery on the Parent form but it still contains data when I open it again.

Any help would be appreciated.

Private Sub btnVendorReport_Click()
    Dim strFilter As String

    If Not IsNull(Me!DemoClientID) Then
        strFilter = "DemoClientID = " & Me!DemoClientID
        
        If Me.FilterOn And InStr(Me.Filter, strFilter) > 0 Then
            DoCmd.OpenReport "rptVendorPayment", acViewPreview, , strFilter
        Else
            DoCmd.OpenReport "rptVendorPayment", acViewPreview, , strFilter
        End If
    Else
        MsgBox "DemoClientID is not selected.", vbExclamation
    End If

Exit_cmdCurrent_Click:
    Exit Sub

Err_cmdCurrent_Click:
    MsgBox "Error: " & Err.Description, vbCritical
    Resume Exit_cmdCurrent_Click
    
    Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        ctl.value = Null
    End If
Next ctl
Set ctl = Nothing

End Sub

 

[CJ_London]

Suggest you step through the code to check what actually happens is what you expect to happen

Looks to me like you have an exit sub before clearing the controls

 

[June7]

Why do you need to check status of FilterOn property? How are you filtering form? I don't see any code that applies filter to form. Be aware, Access will save the filter criteria in Filter property if a filter is applied to form. Then if FilterOn is still set to Yes when form opens, it will apply filter. Really don't need to filter and display records on form in order to open a filtered report.

 

[dullster]

Why do you need to check status of FilterOn property? How are you filtering form? I don't see any code that applies filter to form. Be aware, Access will save the filter criteria in Filter property if a filter is applied to form. Then if FilterOn is still set to Yes when form opens, it will apply filter. Really don't need to filter and display records on form in order to open a filtered report.

I hope I'm understanding you right. I filter with a Macro with the following code. Then I have a Master - Child link on ClientID to populate the subform. I use the above code for a button that prints the results of the filter. I would like to clear the data after printing the report or on exit of the form. I have tried On Exit, me.requery but it doesn't clear the data of the subform.

="[DemoClientID] = " & Str(Nz([Screen].[ActiveControl],0))

 

[dullster]

I hope I'm understanding you right. I filter with a Macro with the following code. Then I have a Master - Child link on ClientID to populate the subform. I use the above code for a button that prints the results of the filter. I would like to clear the data after printing the report or on exit of the form. I have tried On Exit, me.requery but it doesn't clear the data of the subform.

="[DemoClientID] = " & Str(Nz([Screen].[ActiveControl],0))

I am using an unbound control. It's the Vendor Payment Search.

I just realized it is not search all records for a Vendor. I will correct that.

 

[dullster]

I have my form fixed. I have tried this code "On Close" and it still doesn't clear the data.

Private Sub Form_Close()
    On Error Resume Next ' Prevent error if subform is not loaded

    ' Check if the subform control exists and is loaded
    If Not Me!frmVendorsearchsub.Form Is Nothing Then
        ' Clear the subform's data
        Me!frmVendorsearchsub.Form.RecordSource = ""
    End If

    On Error GoTo 0 ' Reset error handling
End Sub

 

[dullster]

I have my form fixed. I have tried this code "On Close" and it still doesn't clear the data. I can get the same form to reopen empty in another database. I have compared every setting and they are both the same.

Private Sub Form_Close()
    On Error Resume Next ' Prevent error if subform is not loaded

    ' Check if the subform control exists and is loaded
    If Not Me!frmVendorsearchsub.Form Is Nothing Then
        ' Clear the subform's data
        Me!frmVendorsearchsub.Form.RecordSource = ""
    End If

    On Error GoTo 0 ' Reset error handling
End Sub

 

[arnelgp]

is this what you have in mind?

 

[dullster]

is this what you have in mind?

Exactly. Why didn't I think of On Load. The one thing I didn't try. Thank you

 

[Pat Hartman]

You have no code in the event that opens the report that clears the filter. Do it there.

 

[Ken Sheridan]

I have a form that searches all Vendor payments and the subform produces all the payments to that vendor. I have created a report that prints the results by clicking "Print Report" button. I would like to clear the data on the form when I open that report. Attached is the code that opens the report but I can't get it to clear the data.

I have an orders form whose RecordSource is as follows to restrict the form to orders not yet invoiced:

SELECT
    *
FROM
    Orders
WHERE
    Invoiced = FALSE
ORDER BY
    OrderDate;

In the form a button opens an invoices report at the current order with:

Private Sub cmdCreateInvoice__Click()

    Dim strCriteria As String
    
    strCriteria = "OrderID = " & Nz(Me.Parent.OrderID, 0)
    
    ' ensure current record is saved and open invoice
    Me.Dirty = False
    DoCmd.OpenReport "rptInvoice", View:=acViewPreview, WhereCondition:=strCriteria
    
End Sub

In the report's Close event procedure the following code updates the Orders table to show the order as invoiced, requeries the orders form to remove the order from it and moves the form to an empty new record:

Private Sub Report_Close()

    Dim strSQL As String
    Dim strCriteria As String
    
    strCriteria = "OrderID = " & Nz(Forms("frmOrders").OrderID, 0)
    strSQL = "UPDATE Orders SET Invoiced = TRUE WHERE " & strCriteria
    CurrentDb.Execute strSQL
    
    Forms("frmOrders").Requery
    DoCmd.GoToRecord acForm, "frmOrders", acNewRec
    
End Sub