Auto update from one table to another table

[ckleung]

I have 2 table that have the same field [DischargeDate], I want to auto update Table2 when I type in the data in table1. I write the following code, but it didn't work.

Private Sub txtDischarge_AfterUpdate()
Dim cnnDB As New ADODB.Connection
Dim rsObject As New ADODB.Recordset

Set cnnDB = CurrentProject.Connection
rsObject.Open "MedicalInformation", cnnDB, adOpenStatic

With rsObject
.Find "[EngName] = 'Me![EngName]'"

.Update "#[DischargeDate]# = #Me![DischargeDate]#"

End With

Can anyone help

 

[Kraj]

I realize there may be a reason why you don't want to do it this way, but wouldn't it be easier to make a relationship between the two fields? That way the data would always be the same in both spots.

 

[ckleung]

Thanks

Thanks for your advice. Maybe I do it too complicated.