Filter Records in subform based on a combo box

[hercule_p2001]

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.

 

[SydneyGeek]

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]

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]

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]

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]

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]

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]

Doesn't work. Tried it.

 

[hercule_p2001]

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.