I was trying to help out in a UA thread:

How to toggle between tabs in Access programmatically

I have tabbed Access. I have a main form where the whole shebang happens.

On that form is a button that opens a report (as another tab)

What I need is when the user double clicks on the field on that report to switch back to the Main form - it should be in focus.

The Question?

The question was:

How can one switch to a specific tab, when using Tab Documents?

My Original Answer

One way, that I learnt a long time ago by mistake, was that you can simply try to open the object again.  If it is already open, Access simply switches to the existing instance.

Thus, you can simply use the DoCmd.Open….

So for a Form, you could do:

DoCmd.OpenForm "YourFormName"

Or, for a report:

DoCmd.OpenReport "YourReportName"

An Alternate Approach

I thought I’d quickly share another possible way to handle this using UIAutomationClient.  You could use a function such as:

'---------------------------------------------------------------------------------------
' Procedure : MDIClient_SwitchObject
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Change Document Tabs, object focus
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: UIAutomationClient
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sObjectName   : Name/Caption of the object to set the focus on
' sClass        : Class of the object to set the focus on
'                   OTable = Table, OQry = Query, OForm = Form
'                   OSplitView = Split Form, OReport = Report, OScript = Macro
'
' Output
' ~~~~~~
' Boolean -> True  = operation was successful
'            False = operation failed
'
' Usage:
' ~~~~~~
' Call MDIClient_SwitchObject("ProjectCategories", "OTable") 'Table
' Call MDIClient_SwitchObject("Past Due Invoices", "OQry") 'Query
' Call MDIClient_SwitchObject("Contacts", "OForm") 'Normal Forms
' Call MDIClient_SwitchObject("OrderDetails", "OSplitView") 'Split Forms
' Call MDIClient_SwitchObject("Accounts Accruable", "OReport") 'Report
' Call MDIClient_SwitchObject("AutoExec", "OScript") 'Macro
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-07-07              Initial Release
' 2         2022-07-11              Added OSplitView for Split Form per AHeyne's comment
'---------------------------------------------------------------------------------------
Public Function MDIClient_SwitchObject(sObjectName As String, sClass As String) As Boolean
    On Error GoTo Error_Handler
    Dim oUIA                  As UIAutomationClient.CUIAutomation
    Dim oAccess               As UIAutomationClient.IUIAutomationElement
    Dim oCondition            As UIAutomationClient.IUIAutomationCondition
    Dim oObject               As UIAutomationClient.IUIAutomationElement

    Set oUIA = New CUIAutomation
    Set oAccess = oUIA.ElementFromHandle(ByVal Application.hWndAccessApp)

    If Not (oAccess Is Nothing) Then
        Set oCondition = oUIA.CreateAndCondition(oUIA.CreatePropertyCondition(UIA_NamePropertyId, sObjectName), _
                                                 oUIA.CreatePropertyCondition(UIA_ClassNamePropertyId, sClass))
        Set oObject = oAccess.FindFirst(TreeScope_Subtree, oCondition)
        If Not (oObject Is Nothing) Then
            oObject.SetFocus
            MDIClient_SwitchObject = True
        Else
            Debug.Print "'" & sObjectName & "' not found."
        End If
    Else
        Debug.Print "Can't locate the database windows."
    End If

Error_Handler_Exit:
    On Error Resume Next
    If Not oObject Is Nothing Then Set oObject = Nothing
    If Not oCondition Is Nothing Then Set oCondition = Nothing
    If Not oAccess Is Nothing Then Set oAccess = Nothing
    If Not oUIA Is Nothing Then Set oUIA = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: MDIClient_SwitchObject" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

The above actually works for both Overlapping Windows and Tabbed Documents alike.