filter by recordset in combobox

[Neil_Pattison]

I need to be able to use a combo box on a form to filter the records shown. The fields on the table are as follows:

ID No, Ref No, Start Date, Comp date, Price etc.

I have set the form up to run off a query that shows all records with a certain ID No. Each ID No has a number of reference numbers. I would like to use a combo box on the form so the user can select a Ref No and the form would be filtered to show only the details of that specific ref no.

What is the easiest way to do this?

Any help would be greatly appreciated.

 

[cagay]

Create your combo box by the combo wizard and drop it into your form header, then just follow the onscreen instruction..(be sure to select the option "Find a record"..

Hope this will help you,

 

[Neil_Pattison]

thanks for your reply but unfortunatly this isn't working. Do you have any other ideas?

 

[cyberpac9]

here's what i've done (assuming you're comfortable with VBA):

unbound combobox on main form....the source is a query and the proper "bound field" - bound field being the link between the main form's table and the subform's table (table1.id and table2.id, for example).

then, in the afterupdate for the combobox, write VBA code to do a search where table2.id = table1.id and "requery" the subform...

note: i've never had 2 bound fields, which you will need according to your example. you said that one ID no will have multiple ref no...so, you'll have to find the record in the subform where id no's are equal AS WELL AS the ref no....

 

[ScottGem]

I need to be able to use a combo box on a form to filter the records shown. The fields on the table are as follows:

ID No, Ref No, Start Date, Comp date, Price etc.

I have set the form up to run off a query that shows all records with a certain ID No. Each ID No has a number of reference numbers. I would like to use a combo box on the form so the user can select a Ref No and the form would be filtered to show only the details of that specific ref no.

What is the easiest way to do this?

Any help would be greatly appreciated.

The way I do this is to base the form on a query. I then add an UNBOUND combobox that will store the ID #. In the query I set the criteria for the ID # to:

=Forms!formname!controlname

where formname is the name of your form and controlname the name of the unbound control.

Finally, in the After Update event of the unbound combo place this line of code:

ME.Requery

to requery the form.