Search form

I am looking for advice.  I have a table with 125 fields believe it or not.  It comes from an Excel file import.  I need to create a way to allow the user to search from data records in any of the fields.  In the past I've just created a form with fields to search by and then created a query with those fields and then for the criteria for each field I've used, for example:

Like "*" & Forms!frmName!txtField1 & "*"

Is there a better way?  We want to be able to search on any of the fields or any combination of the fields.  But a form with 125 fields to search by?????

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A Datasheet form allows you to search from each column header:

Access Search
You can't do combinations using that method, but can use incremental searches. So if you search for "Dell" in the CompanyName field, you could then further search for "Florida" in the State field, and so forth.

If you want combos and such instead, you've got a lot of work ahead ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
You could create a listbox and use the RowSourceType as "Field List", and set the RowSource to your table.  Then you would set the MultiSelect option to Simple or Enhanced to allow your users to select multiple fields from the list to search on.

Then you would have a textbox to enter the value you want to search on, and a command button to perform the search.  When the user clicks the command button, you would loop through the list of fields selected in the listbox and construct a Filter string.  Something like:

Private Sub cmd_Filter_Click

    Dim varItem as variant
    Dim varFilter as variant

    if me.lst_FieldNames.ItemsSelected.Count = 0 then
        msgbox "You must select one or more fields to perform the search"
        Exit Sub
    Elseif me.txt_SearchFor & "" = "" Then
        msgbox "You must enter a search string to search for."
        Exit Sub
    End if    

    For each varItem in me.lst_FieldNames.ItemsSelected

        varFilter = (varFilter + " OR ") _
                   & "([" & me.lst_FieldNames.columns(0, varItem) & "] " _
                   & "Like '*" & me.txt_SearchFor & "*')
    Next varItem

    if Len(varFilter) = 0 then
        me.Filter = ""
        me.FilterOn = False
        me.Filter = varFilter
        me.FilterOn = true
    End If

End Sub

Open in new window

This particular example uses "OR" to concatenate the various sections of the filter, so it would find any record where the search value is found in any of the selected records.  You could also add a combo box with options for "OR" and "AND" and modify the code so that you could search for records where the same term was used in more than one field.
I would use the built in tool recommended by Scott if the recordset was only a few thousand rows.  For a larger recordset, I would create a custom search so I could reduce the number of rows returned.  This is especially important if the BE is not Jet/ACE.

Pick a reasonable max for the number of fields.  Create a form with two combos for each.  So if you decide that 10 is a reasonable number, you'll have twenty combos organized in pairs.  combo1 of the pair is used to select the field to search on.  Combo2 is used to select the value.  You'll have to build the rowsource on the fly or you can just go with text boxes and have the user type in the search value.  You can give the user the option of ANDing or ORing but you can't use them in combination without using parentheses and that greatly complicates the process.

And finally, I would NEVER use LIKE as the relational operator UNLESS, I actually expected the user to enter partial strings.  If they are always entering full values, then use the equal sign.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gustav BrockCIOCommented:
I would educate the users to use the search box:

DoCmd.RunCommand acCmdFind

It does what you want to do and a little more, and any user should be able to manage it.
Combine that with the DatasheetView, and the users have quite powerful tools at hand.

SteveL13Author Commented:
Regarding the use of the search box on a datasheet view form, which I would want to open in read-only view...  When the user has entered a string of characters in the search box, is there a way to "refresh" the form to only show those records that have that string of characters in any of the fields?

Then, if so, I'd want to allow the user to double-click the record they want to focus on and have a single-view form with just that record displayed.
Gustav BrockCIOCommented:
I don't think so. The search box will only allow the user to jump from record to record where the item is found.

A single-view form with 125 fields? - I would stick with the datasheetview.

Dale FyeOwner, Developing Solutions LLCCommented:
No, if you want to search across all 125 columns, or a subset thereof, you are going to have to do something along the lines of what I recommended above, although you could use that technique to develop the filter, and then apply it to a datasheet subform.  If I were going to do it this way, I would probably create a popup form to select the columns you want to search in and build the filter string.  I would then apply that string to the datasheet subform and close the popup (or maybe just hide it so that it remains open and available for modifications should you need them - I wouldn't want to have to reselect large numbers of columns each time I wanted to filter records).

And yes, you could use the Form_dblClick event to capture the PK ID of the datasheet record you want to view and then open the single record form for that record, although 125 fields is rather excessive, you might consider using a tab control and grouping the fields by some form of commonality between fields.
SteveL13Author Commented:
So far this is a great information.  I really appreciate it.  But then I had another idea... If I were to create a simple form with a text box that the user enters a string of characters into, and then provided a command button that when clicked would open a datasheet view form with JUST the records that had that string of characters ANYWHERE in any field, how would I code that command button?
Dale FyeOwner, Developing Solutions LLCCommented:
You would still have to build the WHERE clause for the OpenForm method to use, and the syntax for that would be similar to what I posted above.

Question for you.  Are all of the 125 fields text or are some of them numeric? If some of them are numeric, then you would have to take that into account when building this WHERE clause.  My code above assumes that the fields are all text.  To modify that to ignore numeric fields, you would have to open a recordset and use the Recordset.Fields("FieldName").Type property to determine whether the field is text or not.  If it is not text or memo data type, then you would ignore it.
If the 125 fields all contain the same type of information, you have a serious normalization problem.  I know you said you imported a spreadsheet but perhaps you should normalize it and resolve this unnecessarily complicated search.
SteveL13Author Commented:
Thanks to each of you for all the help.  Every single comment was very worthwhile.  I appreciate the advise.  As it turns out I am going to have to post another topic because its gotten more complicated that I though it ever could.  The topic will be named "Create dynamic query form?"
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.