I have about 15 reports in access. 6 of them are required daily with a date stamp. 3 of them are to be saved in one location, the other 3 in another. Because they require the time stamp, I am creating a macro to generate and save the reports in the correct locations.
I have given the reports a good naming convention so I know out of the fifteen, which ones should be daily. In the macro though I dont know how to pull the report names to create a loop which I can use to output the reports. I did create the following query in Access to pull the report names...
"SELECT msysobjects.Name FROM msysobjects WHERE msysobjects.Type=-32764" (called qReports)
... but i cant see how I can use the query name qReports in the macro.
Coding sample is one loop for one location. It crashes on the "Set rstData" line stating "Runtime error #13 Type Mismatch".
I think it should be fairly straightforward to what I what I want to achieve. Let me know if further explanation is required.
strPath = "c:\dailys\"
Dim rstData As Recordset
Set rstData = CurrentDb.OpenRecordset("SELECT ReportName from qReports WHERE ReportName LIKE 'Daily Sales%' ")
Do While Not rstData.EOF
strRep = Trim(rstData!ReportName)
strFile = strRep & Format(Forms.Date, "yyyy-mm-dd") & ".pdf"
DoCmd.OutputTo acOutputReport, strRep, acFormatPDF, strPath & strFile