dynamic query similar to Excel "Find All" result

[andrewjmorin]

I'm pretty new to Access, took a 3 day class a few weeks back. So I know exactly enough to be dangerous. On the plus side, I've done a lot of VBA coding for Excel in the past so I have some understanding of how these programs work under the hood, as we say.

I'm converting/importing a family of Excel spreadsheets into Access 2010. The users are accustomed to using Excel's "Find" binoculars to perform searches through the data to locate, for instance, part numbers. They typically use the "Find All" option, which presents a list of every occurrence of the substring they enter (e.g. a sequence of 4 digits that could appear at any index of any cell's value), and allows them to navigate between the occurrences. The things they search for in this way could be in any column or row.

Once imported into Access, the Access "Find" binocular button does not provide the little sub-table showing all of the found occurrences, just the "next" and "previous" buttons.

Is there a way to structure a Query or a Form to prompt for a search string and then provide the more Excel-like "find all" result?
Or is this one of those things I'm going to have to figure out how to code?
Could I co-opt the Access "Find" somehow and display the list of records it discovers?

 

[Joe4]

Welcome to the Board!

Have a look at Parameter Queries. See this link here: Access 2013: Creating a Parameter Query

When you open the query, it will prompt you to enter what you want to look for. Note that it is attached to a single specific field (column) though. If you are having to search multiple columns, than your database is probably not "normalized" (meaning there is probably an issue with the design of the tables).

 

[andrewjmorin]

Thanks, I feel welcome!

Looking over your link...we covered parameter queries in the class I took, and the fact that it limits the search to a single field is the problem with them.

My users are customer service folks who get calls from people who can only read some part of the identification of our products and are looking for the replacement parts. So we have to search over fields including the Item number, drawing number, notes/description, related reseller part number, etc. I'm not saying I've got this dataset perfectly normalized at this point, I'm still working out how I'm going to ultimately import it all and keep the relations that are built in to the various spreadsheets we've been keeping. However, having all of those fields searched at once is the users' expectation regardless of which table they appear in.

 

[Joe4]

To get it to work the way you want would require VBA, unless you want to manually add the same Criteria to every possible field in might appear in (or have a Parameter for every field where you keep entering in the value you want for each one).

 

[andrewjmorin]

That was my guess, VBA.
Seems a shame since the built-in Find&Replace tool is so close to what I need.

 

[Joe4]

Here's how I would probably approach this.

I would create a "search form", where you enter in the value you want to search for.
I would then create VBA code which would create the needed SQL code "on-the-fly", apply it to the query, and open the query.
It would return all the records which have the values you are searching for (though wouldn't necessarily indicate which field they are found in).

If you want help in doing that, please provide the table name, and the names of all the fields that you need to search.

 

[andrewjmorin]

Your offer is most appreciated! I would love help in creating this thing.

The table in question is tblInvMast, for starters we can limit the search to fields labelled txtItemNo, txtItemDesc & txtDwgNo.
I suppose it will be trivial to expand this to more fields once it is working.

 

[Joe4]

OK. I just helped someone with something like this earlier today. See here for an explanation and details of the logic behind it: vba code with query

First, create a Query and put anything in it (doesn't matter, we will be overwriting it).
Then create a new unbound Form and add a Text Box (name it txtSearchValue) and add a Command Button (name it cmdSearchButton).
Then, add the following VBA code to the On Click event of the Command Button:

Private Sub cmdSearchButton_Click()
'
    Dim mySQL As String
    
'   Build SQL code
    mySQL = "SELECT tblInvMast.* FROM tblInvMast WHERE" & _
            " txtItemNo LIKE " & Chr(34) & "*" & Me.txtSearchValue & "*" & Chr(34) & _
            " OR txtItemDesc LIKE " & Chr(34) & "*" & Me.txtSearchValue & "*" & Chr(34) & _
            " OR txtDwgNo LIKE " & Chr(34) & "*" & Me.txtSearchValue & "*" & Chr(34) & ";"
            
'   Assign SQL code to pre-assigned query
    CurrentDb.QueryDefs("QuerySearch").SQL = mySQL
    
'   Open query with results
    DoCmd.OpenQuery "QuerySearch"
    
End Sub

Then enter the value you want to search for and click the button.
This will create the SQL code you need "on-the-fly" based on your entry, assign the SQL code to your pre-defined query, and then open the query to view your results.

 

[andrewjmorin]

Yours works much better.
I was about to post my WIP code that used a "DoCmd.RunSQL", but it errored out in the "RunSQL."

Can the SQL statement limit which Fields are displayed in the query?
Say: only display the 3 fields we're searching and the index field "longItemID"?
(I promise to bone up on my SQL syntax before asking another one like that.)

 

[Joe4]

Sure. Here it is a little trick that you can use that I mentioned in that thread I linked to.
Build any query you want using the Access Query Builder. Then, if you change your query to SQL View, it will show you the SQL code of that query. That means you don't even need to know how to write SQL code to create SQL code using VBA. Just create an example of the code you want using the Query Builder and SQL View, and rebuild that code in VBA. You can use a Message box to return your SQL string to compare it and make sure you have written it correctly.

So you just replace the wildcard in your SELECT clause with each field name, i.e.
Change:

"SELECT tblInvMast.* FROM..."

to

"SELECT txtItemTo, txtItemDesc, txtDwgNo FROM..."

To add fields, just separate each one with a comma.