access 2010 filtering records from an unbound combo box in vba

Question

I want to be able to filter records in a table, so that just the filtered records are showing in the form I made. I've been trying to do this via an unbound combo box's after_update event, but no luck as yet.

It is just one table that is going to contain 6 fields for each user's finding, plus a memo field containing a physician's observation of a patient. Here's a sample table:

tblFindings( physicians_observation memo, finding_jane int, finding_marc int, finding_lisa int, finding_julia int, finding_jon int, finding_sam int )

Each record has a physician's observation of a patient, each user will determine a finding based on their interpretation of the physician's observation. Each user will enter their finding by selecting an option button from their assigned option group. Each field is bound to an option group; for example Marc's option group is bound to finding_marc.

What I need / want to do is set up a combo box, so each user can filter these records, so either all records are displayed, or just records with incongruent findings are displayed, or just records with congruent findings are displayed in the form. It has to be from this form.

Congruent findings in this case would be where more than 3 users select the same finding, incongruent findings are those where 3 or less users select the same findings - greater variance. There may be instances where no finding was selected, so there will be some null values.

I hope this makes sense, if not I'll try to explain in another way.

Answer 1

Set the combo box up to have the string values you want to filter by.

Then set the subform up to have a recordset sql

Recordset = "Select * from tblfindings where" &cmboxvalue

Put that in the on form load event.

Make sure you bind the table to the subfrom or it will fail but once this is done you can change it as you wish. I would put this on the on form load event.