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?????
--Steve
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
A single-view form with 125 fields? - I would stick with the datasheetview.
/gustav
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.
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.
ASKER
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?
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("FieldNam
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("FieldNam
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.
ASKER
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?"
ASKER
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.