Solved

Search form

Posted on 2014-12-02
11
232 Views
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?????

--Steve
0
Comment
Question by:SteveL13
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
Comment Utility
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 ...
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
Comment Utility
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"
        me.lst_FieldNames.setfocus
        Exit Sub
    Elseif me.txt_SearchFor & "" = "" Then
        msgbox "You must enter a search string to search for."
        me.txt_SearchFor.setfocus
        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
    Else
        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.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
Comment Utility
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.
0
 
LVL 49

Assisted Solution

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

/gustav
0
 

Author Comment

by:SteveL13
Comment Utility
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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.

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 

Author Comment

by:SteveL13
Comment Utility
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?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 

Author Closing Comment

by:SteveL13
Comment Utility
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?"
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now