MS Access - GLOBAL search for keyword (in queries, forms, reports)

Question

[This questions pertains to Access 2003] I need to add an additional criteria (b) to a query wherever another specific criteria (a) is being used, so I need to do a global search for incidences of (a)

So, I can do a global search in VBA code, no problem. To look in queries, I can say:

SELECT MSysQueries.*
FROM MSysQueries
WHERE (((MSysQueries.Expression) Like "*myCriteriaA*"));

But, I can't figure out a way to find any occurences in SQL within a form or report recordsource property.

I suppose I could write VBA to iterate all the objects looking for it, but is there no simpler way to do this? (Hopefully without resorting to a 3rd party add-in, unless its free)

Answer 1

Rick Fisher's Find and Replace gives you two search criteria fields in the free version. I've been using that tool for probably 15 years now. For other solutions see the Scan and replace utilities section at the Microsoft Access third party utilities, products, tools, modules, etc. page at my website.

Answer 2

You can't do it in SQL -- you have to do it in code.

To do so, you walk through the AllForms and AllReports collections and open each in turn in DesignView and check the Recordsource property. You might also want to walk the Controls collection of the forms looking for Combo Boxes and Listboxes so you can check their Rowsource properties. There oughtn't be any of those on properly-designed reports but you should probably check, anyway.

If you need code for this, just ask.