Access Navigation Subform, recordsource

Question

I am working with MS Access and I am currently trying out the navigation sub-forms. However I am finding it difficult to understand how to simply change the recordsource of a sub form. One of the tabs within my "NavigationSubform" is called "nbCustomerList", which has the target navigation name "CustomerList". Within the CustomerList form, there is a button which when clicked opens a popup which allows you to filter the query on CustomerList. How do I achieve a change to recordsource from an event like this?

Private Sub btnSearch_Click()
On Error GoTo HandleError

    If CurrentProject.AllForms("MainMenu").IsLoaded Then
        [Forms]![CustomerList].RecordSource = CustomerListFilter()
        [Forms]![MainMenu]![NavigationSubform].Requery
    End If

''ErrorHandling'''''''''''''''''''''''''''''''''''''''''''''''''''''''
HandleExit:
    Exit Sub
HandleError:
    MsgBox (Err.Number & ": " & Err.Description)
    Resume HandleExit
End Sub

Does this answer your question? Setting the Record Source of a subform in Access — braX
I have tried "Forms!MainMenu!nbCustomerList.Form.RecordSource = CustomerListFilter()" however I receive error 438: Object doesn't support this property or method — Shadyjunior
Does CustomerListFilter() return a valid SELECT statement? A recordsource is more than just a list of things to filter by. — braX
Yes, this works perfectly fine when operating straight from the form, however it throws this error when called from the navigation subform. I have also tried with a standard select * statement and the error still flags — Shadyjunior
Line 5 of the code is calling a function that probably has the code you need to reference to learn this. However, you should google the .recordsource property of subform controls. You can easily set it via code or manually through the properties window. Though, as you will most certainly learn in your research, learning how to properly requery the form is something you'll need to learn. — Doug Coats

June7 June7 · Accepted Answer · 2020-02-03T20:14:23

The following test worked for me:
Forms![Navigation Form].NavigationSubform.Form.RecordSource = "SELECT * FROM Rates WHERE ID=2"

Assuming your form design has the default names of [Navigation Form] and NavigationSubform assigned by Access, in your db try:

[Forms]![Navigation Form].NavigationSubform.Form.RecordSource = CustomerListFilter()

Requery command was not necessary.

I don't use Navigation Form design. Keep in mind that no matter how many tabs are set up, only one subform is available at any time. Nature of Navigation Form is that it loads and unloads subforms as called by click on tabs.

Access Navigation Subform, recordsource

1 Answers