Search code examples
ms-accessms-access-2016

Search for record in navigation subform after navigating from another navigation subform


I have a main form (frmMain) with a navigation control on it (navControl with navSubform). In the navSubform the form frmList is shown. The user selects a record in this list and then clicks on jump to details, after which another form is shown in the navSubform called frmPlant. So far so good. Next, I want to jump in the frmPlant to the correct record, using the DoCmd.SearchForRecord. I fail at achieving this though: either my form reference is unknown or not open.

Private Sub GoToPlant()
    'store selected record ID
    SelectedPlantID = Forms!frmMain!NavSubform.Form!frmList.Form![PlantID]
    'jump to other navigation subform
    DoCmd.BrowseTo acBrowseToForm, "frmPlant", "frmMain.navSubform"
    'set focus to subform
    Forms!frmMain!NavSubform.SetFocus
    'search for record ????
    DoCmd.SearchForRecord acForm, "frmMain.navSubform", acGoTo, "[PlantID] = " & SelectedPlantID
End Sub

Solution

  • Syntax error in setting SelectedPlantID variable. Change to:

    SelectedPlantID = Forms!frmMain!NavSubform.Form![PlantID]

    SearchForRecord doesn't work because frmPlant is not open independently in Forms collection - it is within a subform container control. FindRecord will work:

    DoCmd.FindRecord SelectedPlantID, acAnywhere, , acSearchAll, , acAll