Filtering forms in MS Access

Question

I have a simple form showing products from my database. Each product has a foreign key to manufacturer_id . I would like to filter my form by manufacturer_id instead of default product_id. How I can do that ? I know I must create a macro. Also I've already created a query, that takes manufacturer's name as argument and returns manufacturer_id. So basically it should work in this way, that when I press 'Filter' button on my form, it runs macro that opens my query asking for manufacturer's name. And when the name is returned the whole form is filtered (so somewhere there should be comparison between manufacturer_id in product and that returned from query, but I can't manage to do that). I'm using access 2007.

Model:

Answer 1

This is a basic idea for what you would use for filtering at run time. One sets the filter, the other removes the filter.

Private Sub cmdFilter_Click()
    strFilter = InputBox("Please type an manufacturer ID:", "Filter Criteria")
    Me.FilterOn = True
    Me.Filter = "[manufacturer_id] = '" & strFilter & "'"
End Sub

Private Sub cmdRemFilter_Click()
    Me.FilterOn = False
    Me.Filter = ""
End Sub

You could also do this by changing the RecordSource property of your form off the button event. Something like this:

Me.RecordSource = "SELECT * FROM Product WHERE manufacturer_id = [Please type a manufacturer ID:]"

To set it at design time, you can simply put your conditions in the Filter property of the form or better yet, use the conditions in the query the form is built from.