I have any application with multiple tabs (see below).

2021-07-16.jpg


The aim is complete the Contact Tab before allowing access to the other Tabs, which I have successfully achieved using the following code embedded in the Change Event of the the Tab Control associated with this form (code below).

Private Sub TabCtPersonnel_Change()
If glbHandleErrors Then On Error GoTo ErrHandler ' Set Error Handling


    Dim dbs As DAO.Database ' Dimension Database
    Dim rs As DAO.Recordset ' Dimesion Recordset
    Dim strSQL As String ' Dimension SQL Statement
    Static Counter As Integer
    
    Set dbs = CurrentDb ' Initialise a reference to the current database


    strSQL = "SELECT tblStatus.pkStatusID, tblStatus.fkPersonID FROM tblStatus " _
           & "WHERE NZ(tblStatus.fkPersonID,0) = " & Nz([Forms]![frmPersonnel]![pkPersonID], 0) & ";"
             
    Set rs = dbs.OpenRecordset(strSQL) ' Initialise Recordset to determine if Available.


' 1. CHECKS TO SEE IF CONTACT DETAILS HAVE BEEN COMPLETED, PRIMARILY SURNAME FIELD.
    If IsNull(Me.txtSurname) Then
            Me.TabCtPersonnel = 0 ' Moves to Contact Tab
            MsgBox "Complete Surname in Contact Tab before accessing other Tabs"
            GoTo ExitHere:
     End If


' 2. CHECKS TO SEE IF STATUS INFO SUB FORM HAS BEEN FILLED OUT CORRECTLY.
    If rs.RecordCount = 0 Then
        Me.TabCtPersonnel = 0 ' Moves to Contact Tab
         MsgBox "Complete Status and Start Date fields of Status Info Sub Form to Access Other Tabs"
        Me!frmStatusSubForm.Form.txtStatusDesc.SetFocus ' Returns focus to first field of Status Info Sub Form
    Else
        ' Checks to see that BOTH Type and Start Date fields are complete.
        If IsNull(Me!frmStatusSubForm.Form.txtStatusDesc) Or IsNull(Me!frmStatusSubForm.Form.dtmSStart) Then
            Me.TabCtPersonnel = 0 ' Moves to Contact Tab
            MsgBox "Complete Status and Start Date fields of Status Info Sub Form to Access Other Tabs"
            If IsNull(Me!frmStatusSubForm.Form.txtStatusDesc) Then
                Me!frmStatusSubForm.Form.txtStatusDesc.SetFocus
            Else
                Me!frmStatusSubForm.Form.dtmSStart.SetFocus
            End If
            GoTo ExitHere:
        End If
     End If
  
' 3. CHECKS TO SEE WHICH TABS USER HAS ACCESS TO DEPENDENT ON SECURITY LEVEL AND TYPE OF PERSON STAFF, LEARNER, ETC.
    Select Case Me.TabCtPersonnel.Value
      Case Me.pgContact.PageIndex ' Contact Tab


      Case Me.pgPersonal.PageIndex ' Personal Tab


      Case Me.pgLearner1.PageIndex ' Learner Tab
        If Not Forms("frmPersonnel").Form("frmStatusSubForm").Form("txtStatusDesc") Like "Learner" Then
            Forms!frmPersonnel.TabCtPersonnel = 0 ' Moves to Contact Tab
        End If


      Case Me.pgStaff.PageIndex ' Staff Tab
        If Not Forms("frmPersonnel").Form("frmStatusSubForm").Form("txtStatusDesc") Like "Staff*" Then
            Forms!frmPersonnel.TabCtPersonnel = 0 ' Moves to Contact Tab
        End If
        
      Case Me.pgVolunteer.PageIndex ' Volunteer Tab
        If Not Forms("frmPersonnel").Form("frmStatusSubForm").Form("txtStatusDesc") Like "Volunteer" Then
            Forms!frmPersonnel.TabCtPersonnel = 0 ' Moves to Contact Tab
        End If
        
      Case Me.pgHumanResource.PageIndex ' Human Resources Tab
        ' Only Allows Security Level of >8 AND doesn't allow Person to see own record.
        If Forms!frmLoginScreen!numSecurityLevel < 8 Or Me!pkPersonID = Forms!frmLoginScreen!fkID Then
            Forms!frmPersonnel.TabCtPersonnel = 0 ' Moves to Contact Tab
        Else
        ' Only show record if Persons Login Security Level is < Person Logged in.
            strSQL = "SELECT tblPerson.pkPersonID, tblLogin.fkID, tblLogin.numSecurityLevel " _
                   & "FROM tblPerson LEFT JOIN tblLogin ON tblPerson.pkPersonID = tblLogin.fkID " _
                   & "WHERE IIF(IsNull(tblLogin.numSecurityLevel),0,tblLogin.numSecurityLevel)<" & Forms!frmLoginScreen!numSecurityLevel & " " _
                   & "AND tblLogin.fkID=" & Forms!frmPersonnel!pkPersonID & ";"
                     
            Set rs = dbs.OpenRecordset(strSQL) ' Initialise Recordset to determine if Available.
            
            rs.MoveFirst
            
'           Do While Not rs.BOF And Not rs.EOF
                If rs.NoMatch Then
                    Forms!frmPersonnel.TabCtPersonnel = 0 ' Moves to Contact Tab
                End If
'            Loop
       End If
                
      Case Me.pgLearner.PageIndex ' SnapShot Tab
        If Not Forms("frmPersonnel").Form("frmStatusSubForm").Form("txtStatusDesc") Like "Learner" Then
            Forms!frmPersonnel.TabCtPersonnel = 0 ' Moves to Contact Tab
        End If
        
      Case Me.pgAttendance.PageIndex ' Time Tab


      Case Me.pgAdmin.PageIndex ' Admin Tab


    End Select


  
ExitHere:  ' Any Error Clean Up Code
   rs.Close
   dbs.Close
   Set rs = Nothing
   Set dbs = Nothing
   Err.Clear
   Exit Sub
  
ErrHandler: ' ERROR HANDLING ROUTINE.
   If Err.Number <> 0 Then
       Call LogError(Err.Number, Err.Description, Forms!frmLoginScreen!fkID, Environ("UserName"), Environ("ComputerName"), "", glbHandleErrors)
      Resume ExitHere
   End If


End Sub
Problem I have is the the MsgBox lines of code (highlighted red) appear TWICE rather than the ONCE.

Any idea what is causing this, debugging shows that it hits the MsgBox line fires, goes to ExitHere: then circles back around to the MsgBox again before returning to the Contact Tab. If you look at ExitHere: its set up to Exit Sub at the end so why is it looping?