Access 2007 Searching and/or filtering issues

[cully651]

Hi Guys,

As most of you have seen, Access 2007 is quite different in regards to the searching and filtering capabilities.

Searching is very fast now, but it only finds the fist instance of a string within the master records, starting at the beginning of the recordset. There is no "find next" or nav button(s) available to continue searching. Searching is useless unless you are 100% sure that the string only exists in one record.

Filtering now assumes you intend to "And" each filtering criteria on to the last one until you reset the filtering. If your recordset doesn't have anything that matches the filter, the form just goes blank and appears to lock up. I've made workarounds to this (got rid of the "And" logic and trapped the No Records issue) but it's still really clumsy for the users to use.

I'm looking for suggestions/solutions that others have created to improve the searching and/or filtering so that users can find records quickly and confidently.

Thanks!

 

[dhookom]

I use forms/controls for all user interaction. Results are displayed in forms and/or reports.

Duane
Hook'D on Access
MS Access MVP

 

[cully651]

I'm talking about forms and Access controls. They right click on a field to filter it. This actually adds another "And" to the previous filter. The code workaround I've come up with makes the filtering OK... not great. Similar to older version of access. The searching is the real puzzler for most users. I think it's really nice, but what I see users doing is searching (for instance typing in a last name) and then expecting that there will be a "find next" function somewhere. We've had too many dupes and other issues because people don't understand the search (they were used to older versions). In Access 2007 there isn't a property to shut off the searching (hide the search box) without hiding the nav buttons too.

 

[dhookom]

I guess I have always created controls and code to provide the searching. Some users might use the right click functionality but I doubt most even know it's there.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Like Duane I always provide the user robust searching and filtering capability. There are lots of ways to do it, I use a consitent strategy because it can be reused throughout the db by forms, reports, queries, and code.

I build robust filter/search forms that return a where string. These forms are pop up. They have an Ok and Cancel button. If the form is canceled it is closed, if Ok then it is hidden.
I wrap the call to these forms in a function like "getFilter". The function pops it open dialog, when the form closes it checks if it is loaded (hidden) and returns the filter string from the form. If it is closed the function returns nothing. Then the string can be used to filter a form or report or used in a sql string.

Here is an example about as robust as it gets.

http://www.4shared.com/document/vskupE9D/MajP_Tool_Log_AdvancedSearch.html

The ability to sort the list by any column, alone allows the user to find a record almost immediately. The other controls then allow the user to filter the list further as either and or or. Then the user can pick a single record from the narrowed list or use the filter.


Now the filter form can be as robust as you want. I could be a simple combox to choose a Name, or a single multi-select list box to choose several records. At the extreme it has combo boxes for different fields, multi select list box, a choice for an "and" or "or" search. A tabular display showing the records as they are narrowed down, sortable columns to sort the list. And a choice to pick a single record and "goto" vs filter.

However

We've had too many dupes and other issues because people don't understand the search (they were used to older versions).

No that is wrong. You have too many dupes because of poor database design. The user forced to search for a possible duplicates, you should design using proper keys and data verification to disallow the user from entering a duplicate or to prompt the user of a possible duplicate.

 

[cully651]

No that is wrong. You have too many dupes because of poor database design. The user forced to search for a possible duplicates, you should design using proper keys and data verification to disallow the user from entering a duplicate or to prompt the user of a possible duplicate.

They do the search in order to update current records. Only after they have concluded that the record does not exist do they add a new one. The built in search only finds the first occurrence of anything and is therefore useless (aka dangerous). The Access 2007 search box cannot be disabled without disabling the nav buttons. Users that used versions of Access prior to 2007 enjoyed a very different search functionality. The database design is as tight as it can be and still meet the business needs.

It looks like disabling the nav buttons and replacing them with additional buttons, along with building some "find first, find next" searching is going to be the solution.

 

[boblarson]

By the way, if you add your own RIBBON (or modify an existing one) you can use the FILTER BY FORM functionality (you can also add that to the QAT) and then they can use that for anything they'd like including OR'S

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[cully651]

Personally, I like the idea of using the ribbon but the users hate it. Not sure yet if it's just a "getting used to it" thing or if it's really a giant UI mistake.

I heard custom ribbons will be better in Office 2010 (which we will be using) so I'm going to stay tuned for that info.

The QAT is the closest thing I can find to the old toolbars and my users would love to have those back.

 

[boblarson]

I would start getting them used to the Ribbon because it isn't going away. I personally like it and have found that it is more functional for me than the old toolbars/menus but AFTER you get used to them. It does take a while to get to know where the commands are located, but for me it is now something I look forward to at work because we don't have 2007 or 2010 yet but I use 2010 all of the time at home.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com