Filter Records in subform based on a combo box

hercule_p2001

New Member
Joined
Feb 26, 2009
Messages
18
Ok. I have gone through the questions on this subject but I am still unable to get my form working. Nothing happens. I'll explain.

I have a table Patients and a table FollowUp. PatientID is the PK which is a text. The FollowUp table has records of patients who visit me(I am a doctor). The PtName field in the FollowUp table is a lookup field which is a 2-column combo with Bound Column 1 but showing the 2nd column which makes more sense as I can read the Patient's name and select while filling up the details. What I need is to create a separate form which has a combo box populated with the list of patients from the Patients table(again this unbound combo box hides the ID column). A subForm is created from the followUp table. I need to select a name from the combo box and be able to see all the visits the patient has made to date. But filter doesn't work, recordset clone doesn't work. I am frustrated. Can anyone help me to sort this out?

This is the Row Source for the form:

SELECT FollowUp.ID, FollowUp.dDate, FollowUp.PtName, FollowUp.Diagnosis, FollowUp.F_Up_After, FollowUp.F_Up_Date, FollowUp.FUpsOnTheDay, FollowUp.Notes, FollowUp.LMP, FollowUp.FurtherManagement, FollowUp.F_Up_NotPrecise FROM FollowUp;

The FollowUp table has essentially the same fields. PtName is a lookUp field as I said before.

The Patients Table has 2 fields:
PatientID
PatientName

I am using Access 2010.

Thanks.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
In design view on the form, click the subform ONCE and immediately go to the Properties window.
In the Data tab, you should see Master and Child fields. Make sure that the Master field is PtName, and the Child field is PatientID.
The other thing is that those 2 combos MUST be bound. Then, when you switch to a new FollowUp record the subform will sync with the corresponding patient.
Another tip: DON'T use lookup fields. They may make your initial setup simpler but they cause heaps of problems. Ypu're much better off making it a number field (for the ID), and then building a combo for the display. See this link for info on why not to use Lookup fields: The Access Web - The Evils of Lookup Fields in Tables

Denis
 

hercule_p2001

New Member
Joined
Feb 26, 2009
Messages
18
Thanks a lot for the reply and the pointer. I'll try this out today and post the outcome. But I have a question here. Hoe do I make the 2 combo's bound? Because when I put the a combo on the header of the form, an unbound combo is created. Also I want this display for viewing only. Strictly not editable.

Thanks once again.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Let's say you want to see the Patient Name, and you have a PatientID field.
1. In design view, right-click the text box for PatientID and change to ... combo box.
2. In the combo's properties, go to the Data tab. The Control source will be PatientID; leave that as it is. The Row source type should be Table / Query; click the ... button at the right of the Row Source to go the a query design window. Now grab the Patients table and add the PatientID and PatientName fields. If you want you can sort by Patient Name.
3. Go to the Formatting tab. Set up the following properties:
Column Count -- 2
Bound Column -- 1
Column widths -- 0cm;4cm
List width -- Auto

You will now have a bound combo that stores PatientID and displays the Patient Name.

Denis
 

hercule_p2001

New Member
Joined
Feb 26, 2009
Messages
18

ADVERTISEMENT

Doesn't work. I cannot select any value from the dropdown combo. Is there any way I can do the entire exercise through VBA? Like set the recordset of the subform to show only the records relevant to the PatientID selected from the combo box(unbound).
 

hercule_p2001

New Member
Joined
Feb 26, 2009
Messages
18
I tried to debug the code and what I found was surprising. When I hard code the SQL statement in the the Recordsource property of the subform, I get the desired records. When I pass the criteria from the unbound combo-box I get a proper SQL statement:

"SELECT FollowUp.ID, FollowUp.dDate, FollowUp.PtName, FollowUp.Diagnosis, FollowUp.F_Up_After, FollowUp.F_Up_Date, FollowUp.LMP, FollowUp.Notes, FollowUp.FurtherManagement, FollowUp.F_Up_NotPrecise FROM Patients INNER JOIN FollowUp ON Patients.PatientID = FollowUp.PtName WHERE FollowUp.PtName = 'J0309';"

All proper quotes and all but the subform doesn't show any changes. It shows all records. I paste this above statement into the RecordSource property and run the subform I get the records for the said patient. So why is this taking place?
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

Try refreshing the subform in code.
in the AfterUpdate event of the combo you'll need a line like this:

Me.MySubform.Form.Requery

Change MySubform to suit the name of your subform.

Denis
 

hercule_p2001

New Member
Joined
Feb 26, 2009
Messages
18
I managed to solve this problem in a different way. I created a subForm of FolloUps Table. Saved it(datasheet view). Created another form from FollowUps Table but this time deleted all fields except the PtName field. Placed the subForm in this form and linked the PtName from both as Master & Child. Now I get what I wanted. Except that the Patient names are in ascending order by their ID's not their names. Otherwise working fine.

Thanks.
 

Forum statistics

Threads
1,141,867
Messages
5,709,086
Members
421,614
Latest member
RAB29

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top