Automatically updating a field

Posted on 2011-10-03
Last Modified: 2012-08-14
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.
Question by:Chrisjack001
    LVL 84

    Accepted Solution

    Add code like this in the AfterUpdate event of your cmbPatientName:
    Me.txtDOB = DLookup("DOB", "Patient", "PatientID=" & Me.cmbPatientName.Column(0))

    Open in new window

    Also add the same code in the Form_Current event.

    Remove the ControlSource value from the DOB textbox.
    LVL 13

    Expert Comment

    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]")

    Author Closing Comment

    Thanks LSMConsulting

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    This article describes some very basic things about SQL Server filegroups.
    Read about achieving the basic levels of HRIS security in the workplace.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now