Entire Table Search Form - Microsoft Access 2010

0
votes

I am trying to create a search form that can search from one table at a time based on the table selected by the user from a combo-box. I have a text box called searchCriteria and the combo-box is called TableList. The idea is that if the user wanted to search customer_table, she would select “customer_table” in the combo box, enter her search criteria in the text box, and press the search button. If she wanted to search order_table, she would select that table from the combo-box and so on. I would like the query to return all full records pertaining to the criteria entered. So, for example, if the user searched “James” from the customer table, I would like the full records (all fields) returned where the criteria “James” is present:

 
    Company               |   First Name    |   Last Name   |   Account #   |   Sales Rep
    Jammy Juice           |   James         |   Dan         |   555         |   Jim Halbert
    Jameson Car Shop      |   Tom           |   Cramb       |   556         |   Dwight Smiles
    Landon Refrigeration  |   Bob           |   McVance     |   557         |   James Bland

From my understanding, this will require some VBA to write a query…

I am assuming the query would be something like the following: SELECT t.* FROM customer_table t WHERE (t.*)::text LIKE '%James%';

Where the string entered (James) and the table name (customer_table) are entered by the user.

Simply, I'm having trouble getting the form data (the table name and search text) into my query.

Does anyone know how to implement this in Microsoft Access 2010? Any insight would be appreciated.

3
sqlms-accessvbams-access-2010

3 Answers

4
votes

The any sequence of characters wildcard in Access is * not %. If you want to search all fields in Access then you could concatenate all the fields:

WHERE [FirstName] & [Surname] & [City] & [Address] 
LIKE "'*" & Forms!frmSearch!txtFind & "*'"

You might separate the fields with a distinct character, to prevent finding words that overlap between the fields.

Or, individually, but less efficient:

WHERE [FirstName] Like "'*" & Forms!frmSearch!txtFind & "*'" 
OR [Surname] Like "'*" & Forms!frmSearch!txtFind & "*'" 
OR [Address] Like "'*" & Forms!frmSearch!txtFind & "*'"

(I don't know where (t.*)::text came from, but it is not Access.)

So you will firstly need to discover all the field-names for the table they have selected. You can obtain the TableDef for the table-name they have selected and loop through its Fields collection to obtain the field-names. There are other ways to do this. For example, GetSchema.

0
votes

Here is the LAZY way of doing this in Access 2010:

  1. Create a continuous form
  2. In the form Properties, set Navigation Buttons = Yes
  3. When you open the form, you will see a Search box:

Search box in Navigation Bar

  1. Type what you want to search for. Hit Enter to see further matches.

Notes:

  • This searches all fields displayed on the form
  • This does not restrict records to the search term
  • This is probably not a good idea for very large recordsets
  • There might be a need to do some user training for this feature
-2
votes

To refer text selected in combo-box

Me.TableList.Column(0)

To refer text in text box

Me.searchCriteria.Text

So your VBA command will look like

DoCmd.RunSQL("SELECT t.* FROM " & Me.TableList.Column(0) & " t WHERE (t.*)::text LIKE '%" & Me.searchCriteria.Text & "%'")