[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Automatically updating a field

Posted on 2011-10-03
Medium Priority
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 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 36904097
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

ID: 36904132
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

ID: 36904258
Thanks LSMConsulting

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

873 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