Search form

Posted on 2014-12-02
Last Modified: 2014-12-03
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?????

Question by:SteveL13
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 40477464
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 ...
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
ID: 40477666
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.
LVL 37

Assisted Solution

PatHartman earned 125 total points
ID: 40477894
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.
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 total points
ID: 40478317
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.


Author Comment

ID: 40478497
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.
LVL 50

Expert Comment

by:Gustav Brock
ID: 40478516
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.

LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40478559
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.

Author Comment

ID: 40478605
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?
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40478674
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.
LVL 37

Expert Comment

ID: 40478698
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.

Author Closing Comment

ID: 40479694
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?"

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question