How to look up values from a table in Access

Question

I have an Access database that is used to store basic info in a table such as first and last name. How would I go about adding the functionality to lookup by last name?

Is there a way to type in the last name and then hit like F12 or something like this? Can someone please point me in the right direction or provide me a link?

Thanks

---

SELECT tblPatient.LName AS [Last], tblPatient.FName AS [First]
FROM tblPatient
WHERE (((tblPatient.LName)=[Enter Last Name]));

How do I tie this into my form now?

Answer 1

You can create queries in Access if the user you're targeting with the searchability has Access themselves.

From the main Access UI (assuming Access 2007), go to the Create tab and then select the "Query Wizard." Here is an article on the subject.

Otherwise you can create a program and connect to the MDB/ACCDB file running the query programmatically.

Answer 2

You just need to create a query in which you put =[?] as the "last name" value.

When you open that view, you'll be asked to type in a lookup value for that field.

Not sure if this is what you are trying to archieve, though...

Answer 3

This is probably a bit overkill for what you want to do, but I assume that you want to perform a search by last name. You should be able to glean the information you need from this article:

Build a search criteria form
http://www.everythingaccess.com/tutorials.asp?ID=Build-a-search-criteria-form

Answer 4

I'd suggest you create a form, with a textbox 'search' at the top, then either a listbox or subform below to display results.

The listbox record source would be:

SELECT tblPatient.LName, tblPatient.FName
FROM tblPatient
WHERE tblPatient.LName LIKE Forms!myForm!search & '*';

You can either add a Search button, which requeries the listbox, or do the requery via the Change event of the search textbox. The later may be slow if you have a large number of records; if that's the case, you could check that at least 3 (?) characters have been entered before calling the requery.

Answer 5

Seeing you wish to look up a name and populate the form based on the name selected, I suggest you need a combobox. There is even a wizard for doing exactly what you want. To start, you will need a form bound to a table or query, that is a form with a Record Source.

1. Add a combobox to your form

2. Select :

   Find a record on my form based on the value I select in my combobox

3. Select the ID (primary key), Last and First name fields.

4. Access will display an example, suggesting that you hide the Key (id) column. Accept this.
5. Choose a name and finish.

There are a few other small things that could be done for neatness, but you will end up with a form that find the record you want. In addition, the combo will autocomplete if you type in a few letter.

If this is an mde, which your subsequent post seems to suggest it is, there is little you can do wuth out the original file. However, you could try opening the database while keeping the shift key held down and see if that allows you to edit. If you cannot get the original and the shift does not work, you could try rescuing the data, if it, too, is stored in this file.