troubleshooting Question

Access Report Names in a loop inside a Macro

Avatar of Wedmore
Wedmore asked on
Microsoft Access
14 Comments1 Solution236 ViewsLast Modified:
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
    
  rstData.MoveNext
Loop
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 14 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros