Filter my form from combo box

[Angate]

Sorry If this has been posted before, I was unable to find it.

I have a form that contains applications, each application assigned to a department via departmentID. I would like to have a combobox at the top of the form that is populated with all of the departments, and when it is changed, it filters the form to only show applications from that department.



Is this something I do with controls and properties, or do I need to put together some VBA?

[TheShabz]

As I understand, you want a combobox to be populated by a set of department names. and when a department is selected, have a subform to display your results relevant to only that department. Since you didn't mention your experience with Access, I'll assume youre a novice.

Here's the non-vba way to do it (well, one little vba section). VBA will be cleaner and give you more options but I am nowhere near proficient enough to guide you through that one. maybe someone else (probably Maximus) can provide.

the combobox:
1. create a combobox with the wizard
2. "i want the combo box to look..."
3. choose your table/query
4. choose your field
5. hit next until you can hit finish
6. rename the combobox (NOT its label) as something relelvant. we'll call it cmbDept for now.

subform:
1. create a query that will display your results.
2. in the criteria for department (the same you chose as the control source for the combo box) type "Forms!myForm!cmbDept" replacing myForm with your form's name.
3. create a subform using the wizard and use the query you just made as its source

back to the combobox:
1. open the properties dialog box for the combobox
2. on the even tab find After Update
3. click the ellipses (...) to open the vba window
4. type in Me.Requery (press enter) Me.Refresh
5. save and close the VBA window

Now cross your fingers and hope it works because this was all off the top of my head.

[Angate]

You sir, are my hero. It all makes perfect sense, I just didn't know what vba to use to do the refresh I guess.

[TheShabz]

No problem. I'm glad it makes sense. The question is, does it work? If so, please mark the thread solved so others can make use of this.