Autocompleting based on an ID

[Pellikae]

Window's: Access 2000

I'm working on a project which involves entering data into a form from patient medical records. Each patient has their own unique medical record. From another earlier project, we have compiled a good amount of data on some of the patients (date of birth, initials, etc.). These important recurring variables are stored in a table in the same database as the new table as well as the working form.

I am looking for a way to make it so that once a medical record reviewer enters a patient's medical record number it might:

Let NewData(DateOfBirth)=OldDate(DateOfBirth)
Let NewData(PatientInitials)=OldData(PatientIntials)
etc.

I have a feeling this should be accomplished by some event in the AfterUpdate on the MedicalRecordNumber field, but my botched attempts, have ended... botched. There is also the problem that there might be a new Medical Record Number that might not exist from the old database table.

Any suggestions for auto completing, or am I thinking outside of Access?

[whitbacon]

Why not just append the old info to the new table and let not have two tables storing the same info?