Access error: can not add records joint key of table 'TableName' not in recordset (1 Viewer)

[MilaK]

Hello,​

I have two linked Tables 'tblPatients' and 'tblDSA' and two continues forms 'frmPatients' and 'frmDSA'. When I create a new patient via 'frmPatient'I would like to add a new record for that patient in 'frmDSA' without closing the form.​

On 'frmPatients' next to each record there is a button 'SaveNewRecord' that does the following:​

(1)saves a new record to 'tblPatients' and also filters (2) opens 'frmDSA' to display related records to that Patients.​

Here is the filtering code:​

[COLOR=#101094]If[/COLOR][COLOR=#101094]Not[/COLOR][COLOR=#101094]Me[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]NewRecord [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336] DoCmd[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]OpenForm [/COLOR][COLOR=#7d2727]"DSAfrm"[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] _ WhereCondition[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7d2727]"LABCODE="[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#101094]Me[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]LABCODE [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]If[/COLOR]

Here is what happens:​

After the 'DSAfrm' pops up and I try to enter a new record I get the following error."can not add records joint key of table 'TableName' not in record-set"​

The new patient has been save to 'tblPatients' but Access is not letting me add any new records. Please help!​

Here is the code that I use to save a new record:​

​

 [LEFT] Private Sub SaveNewRecord_Click()
  
 Dim db As DAO.Database
 Dim PatientTable As DAO.Recordset
  
 Dim errMsg As String 'Where we will store error messages
  
 Dim errData As Boolean 'Default = False   if we have an error we will set it to True.
  
 Dim i As Integer 'used as a counter in For..Next loops.
 Dim x As Integer 'used as counter in For..Next loops.
  
 Dim errorArray(0 To 3) As String 'Array to hold the error messages so we can 'use them if needed.
  
 'The following If statements correspond with the controls (text boxes/combo boxes/etc) on your form.
  
 If Me.LABCODE.Value = "" Then
             errorArray(0) = "Must Enter Labcode."
             errData = True
 End If
 If Me.LastName.Value = 0 Then
             errorArray(1) = "Must Enter Patient Number"
             errData = True
 End If
 If Me.FirstName.Value = "" Then
             errorArray(2) = "Must Enter Insurance Type"
             errData = True
 End If
 If Me.MRN.Value = "" Then
             errorArray(3) = "Must Enter Intake Nurse"
             errData = True
 End If
  
 'MsgBox "errData = " & errData
 If errData = True Then
   i = 0
   x = 0
     For i = 0 To 3
         If errorArray(i) <> "" Then
             If x > 0 Then
             errMsg = errMsg & vbNewLine & errorArray(i)
             Else
             errMsg = errorArray(i)
             x = x + 1
             End If
         End If
     Next i
  
     MsgBox errMsg & vbNewLine & "Please try again."
     errMsg = ""
     Me.LABCODE.SetFocus
     Exit Sub
 End If
  
 Set db = CurrentDb()
 Set PatientTable = db.OpenRecordset("tblPatients")
  
     With PatientTable
         .AddNew
         !LABCODE = Me.LABCODE.Value
         !LastName = Me.LastName.Value
         !FirstName = Me.FirstName.Value
         !MRN = Me.MRN.Value
         !MRNTwo = Me.MRN2.Value
         Debug.Print Me.MRN.Value
         '!CPI#2 = Me.MRN2.Value
         !Kidney = Me.cbKidney.Value
         !Heart = Me.cbHeart.Value
         !Lung = Me.cbLung.Value
         !Liver = Me.cbLiver.Value
         !Pancreas = Me.cbPancreas.Value
  
         '!DateLogged = Format(Date, "MM/DD/YY")
         .Update
     End With
 'End If
  
 'Let the user know it worked.
 MsgBox "This patient has been added successfully.", vbOKOnly
  
 If Not Me.NewRecord Then
     DoCmd.OpenForm "DSAfrm", _
         WhereCondition:="LABCODE=" & Me.LABCODE
 End If
  
 end sub
[/LEFT]