Duplicate names

[UCLA Lorna]

I have a form to enter data for patients into my table. In the past, I created a button to search records by patient last name using the button wizard to create a record navigation button. However, I had to make sure to repeat the search until I searched the entire table especially if I searched on a popular last name like Smith. In addition, there were several Amy Smiths. Is there a way to search on Smith and have a list pop up all of the Smiths in my database, and then click on the Smith that I wanted? In this list, I would like last name, first name and date of birth so I can identify the correct patient. Thanks.

 

[missinglinq]

The simplest way to do this would be to use a combobox to retrieve the record, letting the combobox wizard do the work for you. The trick here is letting Access know which Amy Smith to retrieve. If your form is based on a query, which all forms should be (even if only one table is involved) you simply go into the Query Design Grid and create a calculated field. In a new field, type in

RetrievalField: [LastName] & " " & [FirstName] & " " & [DOB]

Now, in Design View on your form:

Add a combo box to your form. The Combobox Wizard will pop up

Select "Find a record based on the value I selected in my combobox."

From the query the form is based on, click on the field you're searching by (RetrievalField) to move it to the right side.

Hit Next.

Size the column appropriately.

Hit Next.

Name the combobox.

Hit Finish.

Now you can drop the combobox down and scroll down to the item to search by, or you can start to enter the item, and the combobox will "autofill" as you type. Hit <Enter> and the record will be retrieved.

Linq

 

[UCLA Lorna]

Thank you so much! Worked like a charm.

 

[missinglinq]

Glad it works for you!

Linq

 

[UCLA Lorna]

names with apostrophe

This RetrievalField method worked great in finding patient names in my database, except if my name includes an apostrophe, like O'Donnell. I get an error and the search is abandoned. Suggestions on how to fix this? Thanks.

 

[missinglinq]

If you can tolerate seeing ODonnell in the combobox you could use

RetrievalField: Replace([LastName],"'","") & " " & [FirstName] & " " & [DOB]

or if O Donnell suits you better

RetrievalField: Replace([LastName],"'"," ") & " " & [FirstName] & " " & [DOB]

in your calculated field in the underlying query. Assuming you're actually retrieving your last name in a separate field in your record (as you should) the apostrophe will still show up there, of course, so you'll see O'Donnell.

 

[UCLA Lorna]

Thanks. The query function doesn't recognize the 'replace' function. Any suggestions?