asked on

Automatically updating a field

I have a form called “Scheduling-Add”. When the patient name is selected from the drop down I want the Date of Birth (if available) for that patient to automatically populate in the “DOB” field My form doesn’t do that now. How can I accomplish this goal. Attached is a copy of my database with the form. Thanks in advance.
Invoice-7-7-2001-91611.accdb

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan

Your tables aren't normalized properly to start with. Without going into great detail, your Scheduling table shouldn't have the patients name in it, but rather the PatientID, which would link to the Patient table, where you can then build their name, and also pull in the DOB.

The way you have it can work, using the DBLookup, but its MUCH slower, and you cannot guarentee you won't have issues with matching patients, if a name is spelled differently, or 2 patients have the same name.

=DLookUp("DOB","Patient","PatientID=[PatientName]") Won't work because Patient ID is a number, whereas PatientName is actually (Patient.LastName & ", " & Patient.FirstName & " " & Patient.MiddleName)

There seems to be other codes running that keeps undoing my changes, so I can't verify that this code works. Here's the inferior method you can try:

=DLookUp("DOB","Patient","Patient.LastName & ", " & Patient.FirstName & " " & Patient.MiddleName=[PatientName]")

Chrisjack001

ASKER

Thanks LSMConsulting