form opening with where clause

ginoitalo · December 18th, 2004, 06:39 PM

When I open a form via VBA using a where clause, it opens and filters correctly, but I'm having trouble removing the filter.

I've tried Records->remove/filter sort, and the usuals without luck

Access 2K3 - ADP SQL Serv. 2K

Any ideas ?

jemacc · December 19th, 2004, 02:18 AM

You are using the where clause as your filters to return the records, there fore when you remove it no records will show.

Code:

Sorting:
DoCmd.GoToControl "control at which you want to sort"
DoCmd.RunCommand acCmdSortAscending ' or acCmdSortDescending

Removing filters or sorting:
DoCmd.RunCommand acCmdRemoveFilterSort

Jaime E. Maccou

jemacc · December 19th, 2004, 02:23 AM

In your command button you could issue the following code:

DoCmd.ShowAllRecords

This will remove the filter/sort. Or, you can do this:

Code:

Me.Filter = ""
Me.FilterOn = False

To set another filter do this:

Code:

Me.Filter = "[FieldName] = 'Criteria'"
Me.FilterOn = True

Jaime E. Maccou

mmcdonal · December 20th, 2004, 09:07 AM

When you say you want to remove the filter, are you trying to then re-open the form so that you can see all the records?

It sounds like you are passing a filter on a DoCmd statement from a button, and then once the form is opened and filtered, you want to change to viewing all the records. Is that correct? If so, you could add a button to the form that closes and re-opens in showing all the records.

mmcdonal

ginoitalo · December 22nd, 2004, 09:16 PM

mmcdonal, that is precisely what I need to accomplish, Hopefully Jaime's solution will achieve this, since I'd rather not open and close the form.