Access - Navigation Form Changes Record Source to Target Name and Unbinds Controls

Question

I have a series of forms filtered using VBA all working fine until the forms are added to a Navigation form, then an error is returned saying ' The action or method is invalid because the form or report isn't bound to a table or query'

I think... its caused because the navigation form doesn't have a record source... instead it has a target name

On the original form the property record source is tblAvailableHours2

On the navigation form the property is target name Frm_Available_Capacity_Hours

Its a simple date filter,

Private Sub ApplyDtFilt_Click()

    On Error GoTo ApplyDtFilt_Click_Err
    
     DoCmd.ApplyFilter , "[Start Date] Between #" & Format([AVstrtdt], "yyyy\/mm\/dd") & "# And #" & Format([AVEnDt], "yyyy\/mm\/dd") & "#"
     
    ApplyDtFilt_Click_Exit:
        Exit Sub
    ApplyDtFilt_Click_Err:
        MsgBox Error$
        Resume ApplyDtFilt_Click_Exit
    
End Sub

Is there way of linking the above filter back to the original table tblAvailableHours2 in VBA or manipulating the navigation form record source/target name in the property sheet to get it working again?

Answer 1

I'm not using the navigation forms, but if your form is a subform, you must address this via the subform control holding it:

Private Sub ApplyDtFilt_Click()

    On Error GoTo ApplyDtFilt_Click_Err

    With Me!NameOfSubformControlHoldingFrm_Available_Capacity_Hours.Form
        .Filter = "[Start Date] Between #" & Format([AVstrtdt], "yyyy\/mm\/dd") & "# And #" & Format([AVEnDt], "yyyy\/mm\/dd") & "#"
        .FilterOn = True
    End With
     
    ApplyDtFilt_Click_Exit:
        Exit Sub
    ApplyDtFilt_Click_Err:
        MsgBox Error$
        Resume ApplyDtFilt_Click_Exit
    
End Sub