Selecting one customer from multiple choices

[Carl23]

I have a table with Patient Information. The fields are:
Patient_Number
Patient_Last_Name
Patient_First_Name
I have another table that contains nursing notes. The fileds are:
Patient_Num
Subject
Nursing_Observation
I have a select query that is initiated upon opening a form. The query allows the user to choose the last name. The query criteria is:

SELECT Patient_Information.Patient_Last_Name, Patient_Information.Patient_Number, Patient_Information.Patient_Last_Name, Patient_Information.Patient_First_Name, 
FROM Patient_Information WHERE (((Patient_Information.Patient_Last_Name) Like [Enter Last Name] & "*")); 

The query works. Except when there are multiple patients with the same last name i.e. Jones. I would like the user to select one patient's record to avoid potential entry errors. I have seen and can use a field Patient_Full_Name (=Patient_First_Name+" " Patient_Last_Name).

Also, I have seen VBA code that that uses Me. to select a record and populate a form. Do I need to utilize Me in VBA?
Thanks,
Carl23

[Seth Schrock]

Just curious, why do you have Patient_Last_Name in the query twice?

From your question, I'm guessing that you have a button that opens the form that is based on the query you listed which asks for the last name of the patient. If that is correct, then the results that you give (it doesn't work if there are multiple people with the same last name) would make sense as I believe that Access will automatically use the first record found in the query. What I would do is to create a search results form in datasheet view so that you can see what records are found in the query and then you can select one to open the patient form. I usually make the primary key a hyperlink so that when I click it, it runs the DoCmd.OpenForm command in VBA with the PK as the criteria.

[nico5038]

In general it's a problem to select people by name.
Not only the last name can be duplicate, but even the combination of last and fist name...

Thus I use a mainform with a subform holding all patients with their name, DOB, address and what ever might help to identify the correct patient.

You can add a filter field with a button, but you could also instruct the user to use the right-click pop-up (my favorite).
Next when they position the cursor on the row needed, they can press a button to Update, Print or Delete the patient. This button will than activate the form with the patients data.

Idea ?

Nic;o)

[NeoPa]

I would suggest a ComboBox to filter the form with. The ComboBox would show the [Patient_Number], [Patient_First_Name] and [Patient_Last_Name], but would have the first column as a value. Thus, when an item is selected using all those three fields, the result is the [Patient_Number], which can be used to identify the record uniquely.

I would also go along with Nico's suggestion of using a subform though. Have the [Patient] table as the main form and the [Nursing Notes] table, linked on [Patient_Number] = [Patient_Num], as the subform. You can still have the unbound ComboBox, as described above, to help you find the patient you're interested in. This would typically be held in the Header section of the main form.

[Carl23]

I added the combo box in the header and have the form showing the patient number and full name. The Patient Information table is linked to the subform Nursing observation. How do I have the Patient Infomation and Nursing observation records only show the patient records related to the clicked on record from the combo box? Is this a on click event procedure?
Thanks,
Carl23

[NeoPa]

Carl23:
Is this an on click event procedure?

Indeed it is. Either that, or an AfterUpdate() event procedure ().

See Example Filtering on a Form for an article explaining things for you in full.