Need Help With Auto-Population Access Form from Input Box

[AlvinChadwick]

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.

 

[ruralguy via AccessMonster.com]

Your SSN field is a text field so you need quotes. Here's a pretty good
explaination
on the syntax: http://www.mvps.org/access/general/gen0018.htm

Having said that, normalization rules demand that the data *only* exist in
one table in your system. All you should need in other tables is the
PrimaryKey as a ForeignKey. Then use a join in a query to access all of the
data in the two tables. Post back here if you need further details.