How can I force a navigation control to refresh?-CodePudding

In my Clients form I have 2 textboxes (tbHHName, and tbHHID) and a navigation control (nav) displaying subforms/subreports. When the user clicks tbHHName, a modal form opens to select which client to view. Before switching clients, the form shows:

tbHHName: Client123
nav: Account1, Account2, Account3

After switching to Client456, here's what it displays:

tbHHName: Client456
nav: Account1, Account2, Account3

Here's what it should be displaying:

tbHHName: Client456
nav: Account4, Account5, Account6

After the user clicks on one of the nav tabs it displays correctly, but I can't figure out how to force refresh the nav control without that click. Any advice would be very much appreciated.

All of the nav's subforms/subreports have the NavigationWhereClause set to: "[HH_ID] = tbHHID"

Below is the Form_Load, and the tbHHName_Click subs

Private Sub Form_Load()
On Error Resume Next
    
    'Set form recordsource
    Me.RecordSource = "SELECT * FROM _HOUSEHOLDS WHERE HH_ID = " & GetSetting(AppName, Reg_JBGeneral, Reg_HH_ID, 0)
    
    'Apologize to users
    MsgBox "Please click on the Holdings tab again to refresh the data." & vbNewLine & _
    "      Sorry for the extra click," & vbNewLine & _
    "                            -MaybeOn8", , AppName
    
End Sub

Private Sub tbHHName_Click()
On Error Resume Next
    'open the form that sets new HH_ID value using the SaveSetting method
    DoCmd.OpenForm "Households_Select", WindowMode:=acDialog

    Form_Load
End Sub

Thanks in advance, SO

CodePudding user response：

I tested with UNBOUND combobox sitting on main form to select filter parameter and NavigationWhereClause has criteria expression HH_ID=Forms![NavForm]!Combo9. Code in combobox AfterUpdate event:

Forms![NavForm]!NavigationSubform.Requery

Then I tested with code to change value of combobox and requery.

Forms!NavForm.Combo9 = 17
Forms!NavForm.NavigationSubform.Requery

This all works and does not involve changing RecordSource. Now just decide where to place code that changes control's value and requery subform.

Also, can use DoCmd.BrowseTo method to set focus on particular Navigation tab.

CodePudding user response：

Found a fix. I tacked this onto the end of the Form_Load sub:

Forms("Households").NavigationSubform.Report.FilterOn = False
Forms("Households").NavigationSubform.Report.FilterOn = True

Even though there are mixed subforms and subreports in the navigation control, everything seems to load fine.