Filter subforms date column via main form Date range entry in vba access

Question

I have a form named frmCCAuto which includes my date range i.e. start date and end date text fields with a button to filter date.I have a subform "subCCAuto-Open" (I dont know why my seniors decided to use subform name with '-')whose default view is Datasheet and its record source is pulled from a table tblPayments_Auto. So, I have to filter the records displayed in the subform according to the date range which is in main form. In other words, what I want to have happen is on two unbound text boxes allow users to enter a date range and have the subform filtered to only show records of the date range entered in the main form boxes. Codes I have been working on are:

Private Sub Form_Open(Cancel As Integer)
    Dim db As Database
    Set db = CodeDb

    db.Execute "DELETE * FROM tblPayments_Auto;"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryPayments_AddAuto" (store procedure: sp_qryPayments_AddAuto )
    DoCmd.SetWarnings True

    Me.subOpen.Requery
    ProcessBtn.Enabled = False
End Sub

Private Sub Filter_Click()
    With Forms!frmCCAuto![subCCAuto-Open].Form
        .Filter = "[InvoiceDate] BETWEEN #" & Me.StartDate & "# AND #" & Me.EndDate & "#"
        .FilterOn = True
    End With
End Sub

When I debug my code, it gives me an error saying Microsoft Access can't find the field subCCAuto-Open referred to in your expression. Please help.

Answer 1

Try with the modified syntax:

Private Sub Filter_Click()
    With Forms!frmCCAuto.Form![subCCAuto-Open]
        .Filter = "[InvoiceDate] BETWEEN #" & Format(Me!StartDate.Value, "yyyy\/mm\/dd") & "# AND #" & Format(Me!EndDate.Value, "yyyy\/mm\/dd") & "#"
        .FilterOn = True
    End With
End Sub

where frmCCAuto must be the name of the subform control.