I need help with getting one of my forms to auto populate if a text string is
found in the table.  The Database is used for medical record tracking of
documentation.  The key record is the SSAN (social security number).  I have
a form based on a table called TblRecords and a subform in the form based on
a table called TblPtInfo.

TblPtInfo is the unique patient information and contains no duplicate
information
What I need to happen is for the subform to update automatically if a SSAN is
typed in and is found in the table.  If it is not found then I will have the
form use the add new record.  Right now I cannot get it to work.  this is
what I have used:

Private Sub Text14_AfterUpdate()
   Dim InpSSAN As String
   Dim rsSSAN As DAO.Recordset
         
  ' Capture item code entered
  InpSSAN = Me.Text14
  ' Fetch item record from Item Master table
  Set rsSSAN = CurrentDb.OpenRecordset("SELECT * FROM TblPtInfo WHERE SSAN =
" & InpSSAN)
  ' Check item was found
  If rsSSAN.EOF Then
      MsgBox "New SSAN", vbCritical, "Error"
      rsSSAN.Close
      Set rsSSAN = Nothing
      Exit Sub
  End If
  ' Item found, copy data
  Me.ID = rsSSAN!ID
  Me.FirstName = rsSSAN!FirstName
  Me.LastName = rsSSAN!LastName
  Me.SSAN = rsSSAN!SSAN
  Me.Admission_Date = rsSSAN!Admission_Date
  Me.D_C_Date = rsSSAN!D_C_Date
  Me.Ward = rsSSAN!Ward
  ' Finished with recordset
  rsSSAN.Close
  Set rsSSAN = Nothing

End Sub

This does not work as it always gives me the "New SSAN" error even on a known
SSAN.
I would also like to tie it into the main record to update the SSAN field on
that form.
Please help if you can.  I am new at this and tinkering with other code as I
learn what it is doing.