Filtering Results (1 Viewer)

[SteveF]

Hi guys.

I'm working on a relatively straightforward database to store product cost prices. I might have a product that comes from 3 different suppliers at different cost prices, what I need to do is filter my results by various criteria; product, category or supplier, in order to determine the correct product, supplier and cost price.

I can build the product table no problem, and the form to enter products is not an issue. What I cannot see is how to apply the filters.

In this scenario, how do I apply filters or search criteria? Should I be looking at the table, a query or the resulting report?

Thanks for help.

 

[boblarson]

Which version of Access are you using?

Have you ever tried the FILTER BY FORM command? It is very useful in that you can call that and it lets you put in the criteria into the actual form which you are using for the data. Then you apply filter and it filters it for you based on what you entered into the controls. It may be a bit limiting if you need to select multiple items but for simple searches it works great and you don't have to build anything but just call the command

DoCmd.RunCommand acCmdFilterByForm

and then to apply the filter

DoCmd.RunCommand acCmdApplyFilterSort

 

[SteveF]

Thanks Bob. I've been trying all sorts of things but am not clear where to start. To be clear, would the 'filter by form' command be applied to a query or a table?

That's what I'm looking for. I don't think I need anything overly complex but, as ever, I'm out of my depth and struggling

 

[SteveF]

Sorry, should have said, Access 2007 now.

 

[boblarson]

Take the form where you enter the data.

Add a button and name it cmdFilterData and then in the click event try this:

Private Sub cmdFilterData_Click()
   DoCmd.RunCommand acCmdFilterByForm
End Sub

And then put a button named cmdApplyFilter and use

Private Sub cmdApplyFilter()
  DoCmd.RunCommand acCmdApplyFilterSort
End Sub

And see what happens. (make sure you do it on a COPY of your database so you don't kill something and can discard if it just isn't the right thing for you).

 

[SteveF]

Thanks again Bob. I'm just fiddling around with it again, this has given me a good starting point