Custom coded "find record" vs. built-in filters, which do you use more?

[Isaac]

When creating a form for users to view and/or edit records, we can leave Navigation Buttons on and allow built-in filtering (where you right-click the bound control and select choices like Equals, Contains, etc), or you can custom-code with all kinds of fancy things - cascading dropdowns, textbox "search" boxes, etc.

Plenty of those two areas of design would be totally overlapping and redundant.

I am very curious to know which do you do more of, what do you tend to do more often? Simply allow the built-in Access filters where the user right clicks bound controls, or custom code something special? What makes you more likely to choose one or the other, or more likely to add the custom search features? Is it just when there are too many similar records and the built in Filters aren't quite good enough? Anything else to consider?

 

[Minty]

The right-click filter menu's don't work by default if your users are in a runtime environment.

You can make them work but it's not "out of the box" as a solution.
So I tend to build my own, focussing on what the end-user is likely to search for, predominantly Date ranges, Client names, Employee Names or record 'Status' if that applies.

 

[Isaac]

That's cool. I haven't used the built in filters much (I have to admit) so I did not know that about Runtime - good to know.

In many cases I have a listbox at the top of the form, and detail bound controls at the bottom. I allow custom ways to find, filter & sort the listbox, and then when you click a listbox item, it shows the detail of that single item down below in the bound controls. Wonder if anyone else does that.

 

[tmyers]

I prefer to use hand made filters. I tried the built in ones, and apart from the fact I really dislike the look of them (I really hate the look of the record nav bar and such), I found them clunky. So whenever I need filters, I just do a textbox with an on change filter.

 

[MajP]

I never navigate with nav buttons. My normal approach is I separate navigation (sorting, searching, filtering) from add/edit. My design is normally like Outlook where I have a list and the columns are all sortable (asc, descending). If necessary some additional filter features. Then click on the item to pop up the edit/view details. If this amount of records is relatively small then navigate from a comobbox and if needed a Find as you type combobox.

 

[Isaac]

That sounds kind of like what I have done. And I like a certain degree of separation too. 9x out of 10 that separation tends to align with permissions, anyway.

 

[gemma-the-husky]

for things users need an easy way of accessing, I generally add a search field, and buttons for find first and find next.

So say, an employee name, or a customer name. I know users can use the binocular search but I think that can be confusing for an unskilled user, so where I know they will use the search extensively I give them a field to enter a search string, such as "Smith", and buttons to find successive instances of "Smith" anywhere in the search field.

 

[Pat Hartman]

And I never use filters at all. Most of my BE's are SQL Server or might end up there so I always design for client/server to minimize the pain of having to convert. If I have complex search forms with lots of options, I build the WHERE clause on the fly and concatenate it to the standard select. I run a dcount on the query. If one record is selected, I open the edit form. If multiple records are selected, I open a list form which the user can then filter himself using the built in filters if he needs to. Double clicking on a field then opens the edit form.

If I have only a couple of options, I use unbound textboxes and/or combos on the form's header. and the RecordSource for the form includes a where clause:

Where CustID = Forms!myform!cboCustID or CustOrderDT Between Forms!myForm!txtFromDT AND Forms!myForm!txtThruDT

The form always opens blank. The user enters the criteria and then the form opens to the selected records. If I use one field, I use the AfterUpdate event to requery the form. If I have multiple fields, I use a button to do the requery.

 

[CJ_London]

I do similar to Pat just as (in my opinion) a matter of good practice, so have the form open displaying an empty recordset ( I use the criteria WHERE False) and build a criteria rather than a filter to display selected records.

Sometimes I determine criteria before the form opens, pass it to the openform as an openarg and in the form open event apply the openarg to the recordsource (the where parameter of openform is actually a filter).

The other alternative I use is to open a recordset with criteria applied before the form opens - then once open set the form recordset to the open recordset. As with Pat this has the benefit of being able to do some checks before opening the form and/or using an ADO disconnected recordset so I can add temporary 'unbound' fields such a field for subsequently selecting or matching records (rather than using a temp table) for onward processing. Note that the standard access filtering does not work with ADO so you would have to build your own if you want users to make use of them with an ADO recordset.

with regards the use of Like, I do not add the initial "*" so indexing is still used. But train users to type it themselves if they need to.

 

[Isaac]

Thanks, folks. Very interesting and good to hear all of this. Thanks for contributing.