Multi-Use Search Forms - Discussion on GUI & Methods 2

[khood]

I have an Access 2K application that contains a number of forms that are used for data entry and editing. On all of these forms I have placed a "find record" command button that opens a modal form to do a search, and they can double-click on a record they wish to edit, and it is returned to the original form.

This all works well, but I want to give the users more flexibility on the searches (many different fields) without having to create lookup forms for every possibility. I also don't want a real busy lookup form.

Some of my forms have 20-30 fields on them -- I want to allow them to search on many different fields if they wish. However, I don't want this to create a lot of programming overhead every time they request an additional search field.

I have considered "soft-coding" so that the user can select a limited number of fields to search by. Any suggestions out there?

Thanks,
Ken Hood

 

[RickSpr]

How about developing a generic search form? You could have, say, five rows of 2 combo boxes and a text box each. The first combo would allow to select from a list of fields in the recordset (populated using DAO to scan the parent form's RecordSet). The second combo would be a comparison operator, plus Null and Not Null. The text box would be for entering a comparison value for binary comparison operators.

On clicking OK, you could build a WHERE clause fragment for each row, referring to the field's datatype to determine the syntax for the literal created from the text box. You might even include an AND/OR column on the left (other than for the first row, of course), or you can just assume AND or OR as filter and conditional formatting dialogs do.

By using a Me.Parent reference, you could make it work from any form (provided you want all the fields to be selectable). I think you could even execute the FindFirst on the parent form's RecordSet this way. And the finished form would be a tool you could copy to any database and use unmodified. Rick Sprague

 

[braindead2]

If I understand what you are trying to do! Why not just use

docmd.runcommand acCmdFilterByForm
it will open the form they are currently working on and they can type in the criteria in any of the avaliable fields.

 

[khood]

Thanks for your quick reply. Yes, this is the approach that I was considering. I do have one form that is essentially a "SQL builder", but it is restricted to administrator level users. The client has directed me to assume that the users of these searches are computer illiterate (It's going on a showroom sales floor).

The problem is that some fields I want to keep hidden from the users, or at least disallow searches on these fields. Since the field names and captions are not always what I want to present to them, I would probably want to create a table that contained the searchable fields, and a more user-friendly field name.

Thanks,
Ken Hood

 

[khood]

Braindead,

I have not used filters, so forgive me if this sounds a little naive. What are the limitations of using a filter? What if criteria matches multiple records, how do I allow them to choose the best match? I am going to try this and see how it works.

Thanks,
Ken Hood

 

[scking]

khood,

There are almost as many solutions to this as there are developers. Kind of whatever works best for you. I personally prefer using a custom Shortcut Menu Bar that allows the user to specify the criteria. The user right-clicks the main form and the shortcut filter form pops up. They select the criteria and click 'OK' on the filter form and the main form presents the results.

Filters will display all records that have matched the criteria. If you want them to be able to select a single item then you will need to make the criteria rigorous enough to be able to select one. That could be done by verifying that the user has entered all necessary criteria to proceed to the next step. Then, prompt them if they need to select additional criteria.

Hope this helps.

Steve King

Professional growth follows a healthy professional curiosity

 

[khood]

Everyone that responded - I appreciate your input. Due to the nature of the client user base and their directives, I decided to go forward with developing a full-featured generic search form, as Rickspr is recommending.

I was already leaning this way, but his input gave me a slightly different perspective.

I experimented with filtering, but I don't believe that this is what the client is looking for.

Thanks Again,

Ken Hood