I have been tasked with upgrading an Access application from 2003 to 2010. The application in question has over 1,700 reports. Having converted a development copy from 2003 to 2010, we have stumbled upon a few reports that don't work. We need to find a way to determine if all reports in the database work in the 2003 version before making the determination that a faulty report is due solely to the conversion to 2010.
I am writing a module that will programmatically open each report and write a line to a table indicating whether or not the report loaded successfully. My problem is that many reports rely on either a) queries that have parameters populated from open forms or b) rely on function calls to populate data.
My thought is to loop through each report, checking each report first for function calls, then populating the required variable. Second, I'd obtain the name of the query feeding the report, then figure out how to check the SQL statement for a "forms" or function reference. From there, I'd need to figure out how to temporarily modify the record source, or "spoof" the required parameter.
Is this the right approach to take? It seems a bit clunky, and I'm honestly not certain if it will work.
About all I've got so far is the following, which is simply writing a line to the immediate window (append query will be added later). Clearly I haven't started down the road of writing the above-mentioned two step process. I'd like to get some feedback from the group before I embark on this little expedition!
On Error GoTo Err_Handler
Dim rpts As AllReports
Dim x As Integer
Set rpts = Application.CurrentProject.AllReports
gboolPrintAll = True
For x = 0 To rpts.Count - 1
DoCmd.OpenReport rpts(x).Name, acViewPreview
Set rpts = Nothing
Debug.Print rpts(x).Name & ": " & Err.Number & " - " & Err.Description