Update two tables in recordset

I have the form load event where this code works fine to add employees to the recordset. See form load code.

However, the problem is that the tbl_Personnel and tbl_Appointments are not related to each other in the relationships of the database.
So when I update the tbl_Personnel it does update on the appointment book but all the past and present appointments disappear . In other words I need to find a sql statement that will loop through the recordset and update the employee names in the database, otherwise the appts never will show up on the appt book.

What statement should I use to update the tbl_Appointments so that "employee" name is updated when the user wants to edit the name on the form? I have tried to use a join on the form load but that does not seem to update and the names do not appear on the form in the employee FirstName, LastName txtboxes. when I use :
adoprimaryrs.Open "SELECT tbl_Appointments.Index AS tbl_Appointments_Index, tbl_Appointments.Employee, tbl_Personnel.Index AS tbl_Personnel_Index, tbl_Personnel.LastName, tbl_Personnel.FirstName FROM  tbl_Appointments INNER JOIN tbl_Personnel ON tbl_Appointments.Index = tbl_Personnel.Index ", dataconnection, adOpenStatic, adLockOptimistic
--------------------------------------------

I have for update:
  adoprimaryrs.UpdateBatch adAffectAll

  If mbAddNewFlag Then
    adoprimaryrs.MoveLast              'move to the new record
  End If

  mbEditFlag = False
  mbAddNewFlag = False
  SetButtons True
  mbDataChanged = False

  Exit Sub
UpdateErr:
  MsgBox Err.Description
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

Private Sub Form_Load()
  Dim db As Connection
  Set db = New Connection
  db.CursorLocation = adUseClient
 
   Set adoprimaryrs = New Recordset
 adoprimaryrs.Open "select FirstName,LastName from tbl_Personnel ORDER BY LastName", dataconnection, adOpenStatic, adLockOptimistic
   
  Dim oText As TextBox
  'Bind the text boxes to the data provider
  For Each oText In Me.Txtfields
    Set oText.DataSource = adoprimaryrs
  Next
mbDataChanged = False
End Sub
 
----------------------------------------------------------------------------