Using Command Button to Filter by Form

[johnfrani]

How can I allow the user to use the built-in FilterByForm and ApplyFilterSort menu choices by using a command button instead of using the Access 2000 menu bar.

I want to allow the user to click a "Choose Case Number" button to filter on the Case# DB field and then apply the filter.

Then I would like to know how to have the user click the "Remove Filter" button to remove the filter.

I can currently make this work by clicking the available Access Menu bar choices: Filter By Form icon, click on DB field and select value, click on Apply Filter icon and then click on Remove Filter icon.

Thanks
John

 

[ptpaton]

Build a query that contains all the data you want displayed on the form. Choose a unique field (such as case number) and in the query's criteria for the case number field, reference the case number control on your form, like this:

Forms!frmYourForm!CaseNumber

Call the query "qrysFilterCases"

Create a command button on the form and put this code into the OnClick property:

Private Sub Combo13_AfterUpdate()
DoCmd.ApplyFilter "qrysFilterCases"
End Sub

When you click the button, the applyfilter command will filter all but those records that qualify.

To clear, put a second command button. Caption it "Show All". Put this code on the OnClick property:

DoCmd.ShowAllRecords

That's it! Let me know how it works.

-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[Zion7]

Yes Patrick is correct, just as an option...

Simply 2 statements..

Private Sub cmdFilter_Click()
Me.Filter = "CaseNo =" & Me.CaseNo
Me.FilterOn = True
End Sub

"Me.Filter =" ...is simply a SQL WHERE statement, without the preceding "WHERE". so, you can have...
Me.Filter = "txtName = """ & txtName & """ AND txtAge > " & Me.txtAge ...etc.

To show all, simply put, where appropriate...

Me.FilterOn = False


Good Luck!

 

[johnfrani]

Thanks, Patrick.
It worked!

The new button opens a Parameter Dialog Box that asks the user for the CaseNo. The Show All button removes the filter.

Is there a way to replace the text displayed in the Parameter Dialog box (Forms!frm_Case!CaseNo) with something more helpful for the user to see; example: Enter Case Number Here?

Thanks
John

 

[ptpaton]

Awesome! Glad I could help!

You can customize the user prompt by putting whatever you want in [brackets]. If you type [Enter case number here] into the criteria for the field, the box will pop up with "Enter case number here".

Have you thought about driving the whole thing from a combo box on your form so that the user doesn't have to type anything at runtime, which will greatly reduce errors when running the query?

-Patrick

Nine times out of ten, the simplest solution is the best one.