Filter a subform based on a combo box selection

[david.paton]

I am trying to make a filter to go on a sub form. The form has a sub form that shows legatees and I have a combo box at the top of the screen that I want to be able to select a division from the box and have that division apply as a filter to the query in the sub form. Therefore, the sub form will only show the legatees from the division selected.

Could someone help me in how to do this please?

The form name is frmLegateeList and I have attached my database.


Thanks,
Dave

 

[OriOn]

Hello Dave,

being a bit short in time for a full reply I will share the approach I usually have in this situation.

I use the afterUpdate event procedure of the combobox to collect the new combobox.value and update the recordsource of the subform with a new query (SQL string) more than filtering a larger set of records everytime.

not sure if it's the best approach though.

 

[bob fitz]

I don't understand why you are using the forms that you are using or the way you are using them.
You have set a one to many relationship between Divisions(one) and Legatees(many) so I would have a main form bound to Divisions which has a subform control bound to Legatees. Each Division record would then show only the related records from Divisions.
I would also have a combo box on the main form to find the required Division record.
The attached database demonstrates this concept.

 

[david.paton]

Thanks for that Bob. To tell you the truth, I don't know why I used many of the things I did either. I guess I am still reasonably new to this and don't understand a great deal about access but I think I was just trying to get things working and if it didn't work, i would move onto the next idea and forget to delete the first idea.

Thanks for that solution, it appears to have worked. I had trouble replicating it so I just copied and pasted your form into my database.

 

[bob fitz]

Glad that I was able to help with a solution.

I had trouble replicating it so I just copied and pasted your form into my database.

I assume that if you had problems replicating it that you don't understand exactly what it does. I shall try to explain:

You have a one to many relationship between "Divisions" and "Legatees", so I created a main form bound to the data in the Divisions table(the "one" side). Using the wizard, I then added a sub form to the main form. The sub form is bound to the data in the Legatees table(the "many" side). With that setup there is a record shown in the main form for each Division and the related records from Legatees are shown in the sub form. I put one line of code in the main forms On Open event which moves the focus to a new record when the form opens. If you have many records in the main table, it becomes a pain in the a** to find a particular record. To make this easier I used the wizard to create the combo box which can be used to find any Division by just selecting it or typing it in the combo box.

Hope this explains how it works but if you have any questions feel free ask.