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?

 

[andrewjmorin]

Update:
All of the above has been immensely useful and helpful.
Sorry it took me a while ot get back here and say: "Thank You Very Much!"

-drew