Updating Linked Table

[shep]

MS Office 2003

User enters new record in tblPatientV1 DB EPICS and a record is
automatically created in tblContactsV1 in DB CIMS containing certain fields
from Patient record.

Code
Private Sub Form_AfterInsert()
DoCmd.SetWarnings False
Dim ChartNum As String
ChartNumber.SetFocus
ChartNum = ChartNumber.Text
DoCmd.RunSQL "INSERT INTO Contacts ( LastName, FirstName, MiddleInitial,
Address1, Address2, City, State, Zip, HomeTel, WorkTel,
CellPhone,Email1,Email2,Email3,Email4,Pager, ChartNumber, DateEntered )SELECT
tblPatientV1.LastName, tblPatientV1.FirstName, tblPatientV1.MiddleInitial,
tblPatientV1.Address1, tblPatientV1.Address2, tblPatientV1.City,
tblPatientV1.State, tblPatientV1.Zip, tblPatientV1.HomeTel,
tblPatientV1.WorkTel, tblPatientV1.Email1,
tblPatientV1.Email2,tblPatientV1.Email3, tblPatientV1.Email4,
tblPatientV1.Pager, tblPatientV1.CellPhone, tblPatientV1.ChartNumber, now()
FROM tblPatientV1 WHERE (((tblPatientV1.ChartNumber)= '" & ChartNum & "'));"
DoCmd.SetWarnings True
End Sub

When record is updated in tblPatientV1 in DB EPICS, record in tblContactsV1
in DB CIMS is not being updated.

Code
Private Sub Form_AfterUpdate()
DoCmd.SetWarnings False
Dim ChartNum As String
ChartNumber.SetFocus
ChartNum = ChartNumber.Text
DoCmd.RunSQL "UPDATE Contacts INNER JOIN tblPatientV1 ON
Contacts.ChartNumber = tblPatientV1.ChartNumber SET Contacts.FirstName =
[tblPatientV1].[FirstName], Contacts.LastName = [tblPatientV1].[LASTNAME],
Contacts.MiddleInitial = [tblPatientV1].[middleinitial], Contacts.Address1 =
[tblPatientV1].[address1], Contacts.Address2 = [tblPatientV1].[address2],
Contacts.City = [tblPatientV1].[city], Contacts.State =
[tblPatientV1].[state], Contacts.Zip = [tblPatientV1].[zip], Contacts.HomeTel
= [tblPatientV1].[hometel], Contacts.Email1 = [tblPatientV1].[email1],
Contacts.Email2 = [tblPatientV1].[email2], Contacts.Email3 =
[tblPatientV1].[email3], Contacts.Email4 = [tblPatientV1].[email4],
Contacts.Pager = [tblPatientV1].[Pager], Contacts.WorkTel =
[tblPatientV1].[worktel], Contacts.CellPhone =
[tblPatientV1].[cellphone]WHERE (((tblPatientV1.ChartNumber)= '" & ChartNum &
"'));"
DoCmd.SetWarnings True
End Sub

Help appreciated

 

[shep]

Problem solved, thread counseled.

I found that the key in CIMS was incorrect.