By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

Selecting one customer from multiple choices

P: 22
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Patient_Information.Patient_Last_Name, Patient_Information.Patient_Number, Patient_Information.Patient_Last_Name, Patient_Information.Patient_First_Name, 
  2. 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
Apr 30 '12 #1

✓ answered by 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.

Share this Question
Share on Google+
5 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
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.
Apr 30 '12 #2

nico5038
Expert 2.5K+
P: 3,072
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)
Apr 30 '12 #3

NeoPa
Expert Mod 15k+
P: 31,494
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.
Apr 30 '12 #4

P: 22
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
May 1 '12 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
May 2 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.