How to populate an access database form with values from a search

Question

I have an Access database that I inherited and am trying to add some search functionality to it. This is simply a database that holds patient records such as name, address, phone etc. What I want to do is search by last name and have the rest of the form populate with the information.

It may be worth noting that, for some reason, there is a lock on the desktop icon and when I go to the "form" portion of access to design a form, I am not able to access that as the "design" link is gray shaded. Not sure if these two things are related or not. Right now, there is a single form with nothing but textboxes and a couple of dropdowns. I want to use the "last name" text box to search the table and then populate all other fields. Maybe by using one of the function keys or something like that.

This is the query that works but I have no idea how to tie this into the form. When I run this by itself, I get a dialog box that pops up and I can get the results in tabular format. Again, what I would like is to tie this query into my form.

---

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

Answer 1

In the past I have built a search form where users can search and see results (possibly in a grid) and they select a record which (depending on the complexity of the display and related functionality) can be displayed in a new form or a sub form.

Your point about design mode being grayed out may point to you having an MDE instead of an MDB. MDE is compiled and locked and only the data is editable where as in an MDB file code objects are editable as well. You need the MDB (or ACCDB) to edit code functionality.

Answer 2

If you can edit your access database (see @raj's comments), you might then be able to use the already available "filter by form" tool that allows you, once you've built a form, to automatically use its controls for filtering purposes.

You could also check the 'quite similar' questions available on the site such as:

http://stackoverflow.com/questions/72528/how-would-you-create-a-database-in-microsoft-access-that-is-searchable-only-by-ce

http://stackoverflow.com/questions/1407940/ms-access-passing-parameters-from-one-access-form-to-another

http://stackoverflow.com/questions/333318/access-vbahow-to-get-input-in-forms-and-use-them-in-queries

http://stackoverflow.com/questions/390106/how-do-i-build-a-search-mechanism-for-my-application