MS Access 2016 - Export Reports to PDF with dynamic criteria (seperate by office)
OK, I am out of my element but willing to learn! I created an access database and have a report that I need to export into various folders by office each week.
Not knowing better, I coded each office like this:
DoCmd.OpenReport "OfficeUserReport", acViewPreview, , "[NRT Enterprise Performance].[Branch]='Mar
DoCmd.OutputTo acOutputReport, "", acFormatPDF, "\\...\Atlanta\Office Reports\Marietta-West Cobb Office Report.pdf", False
DoCmd.Close acReport, "OfficeUserReport"
It works, but when an office changes names, gets added or removed, it messes up the reports. How can I create a report to look at a table of offices and "metros" and run the report based off of that?
For example, Table 1 is called NRTOffices. Fields are Office, repOffice, Metro, Region and Address. Table 2 is called NRT Enterprise Performance and has fields called Office and Metro (among many others). *** repOffice is a "friendly version" of the office name that has no invalid characters such as "/", " ' ", "\", etc.
I would like to be able to have the report module run so that it exports each report to \\...\[Metro]\[Region]\Off
Can someone point me in the right direction? Hoping I provided enough detail to make sense.
thanks in advance! I have been racking my brain trying to understand loops but fail each time.
Not knowing better, I coded each office like this:
DoCmd.OpenReport "OfficeUserReport", acViewPreview, , "[NRT Enterprise Performance].[Branch]='Mar
DoCmd.OutputTo acOutputReport, "", acFormatPDF, "\\...\Atlanta\Office Reports\Marietta-West Cobb Office Report.pdf", False
DoCmd.Close acReport, "OfficeUserReport"
It works, but when an office changes names, gets added or removed, it messes up the reports. How can I create a report to look at a table of offices and "metros" and run the report based off of that?
For example, Table 1 is called NRTOffices. Fields are Office, repOffice, Metro, Region and Address. Table 2 is called NRT Enterprise Performance and has fields called Office and Metro (among many others). *** repOffice is a "friendly version" of the office name that has no invalid characters such as "/", " ' ", "\", etc.
I would like to be able to have the report module run so that it exports each report to \\...\[Metro]\[Region]\Off
Can someone point me in the right direction? Hoping I provided enough detail to make sense.
thanks in advance! I have been racking my brain trying to understand loops but fail each time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm not sure what you are trying to update. The code opens a recordset and reads it. It does not update it. What are you doing when you get the error? If you are running code, please post it. If you are running a query, please post it.
ASKER
Not updating anything that I can tell... I am running this:
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim FileName As String
Set db = CurrentDb()
Set qd = db.QueryDefs!qUniqueOffices
Set rs = qd.OpenRecordset
Do Until rs.EOF = True
Me.txtHiddenOffice = rs!repOffice
FileName = "\\vmware-host\Shared Folders\edward.hamill\Box Sync\Edward Hamill\Performance Reports - 2016\Reports\" & rs!repMetro & "\Office Reports\" & rs!repOffice & "OfficeReport.pdf"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, FileName, False
rs.MoveNext
Loop
rs.Close
qd.Close
On what line does the error appear? If it is the line that opens the report, please post the query used as the report's recordsource.
ASKER
Appears on "Me.txtHiddenOffice = rs!repOffice
Query qReport
Query qUniqueOffice
Appreciate you not giving up on me yet :-)
Query qReport
SELECT [NRT Enterprise Performance].Name, [NRT Enterprise Performance].MLS, [NRT Enterprise Performance].[Intouch Domain], [NRT Enterprise Performance].[Intouch Email], [NRT Enterprise Performance].[Order Type], [NRT SE Branches].repOffice, [NRT SE Branches].repMetro
FROM [NRT Enterprise Performance] INNER JOIN [NRT SE Branches] ON [NRT Enterprise Performance].Branch = [NRT SE Branches].Branch
WHERE ((([NRT SE Branches].[RepOffice])=[Forms]![yourform]![txtHiddenOffice]));Query qUniqueOffice
SELECT DISTINCT [NRT SE Branches].repOffice, [NRT SE Branches].Metro, [NRT SE Branches].Region, [NRT SE Branches].SubRegion, [NRT SE Branches].Branch, [NRT SE Branches].repMetro
FROM [NRT SE Branches]
ORDER BY [NRT SE Branches].repOffice;Appreciate you not giving up on me yet :-)
The Me.txtHiddenOffice control should not be bound. Remove it's ControlSource. You are just using it as a holding area for Office as the loop runs. The data will not ever be saved.
ASKER
Getting to the point of creating the pdf, but receive a '2487': Object argument for the action or method is blank or invalid
Closer!!!
DoCmd.OutputTo acOutputReport, "", acFormatPDF, FileName, FalseCloser!!!
Hover over the FileName or print it to the debug window so you can make sure it is a valid name.
ASKER
It is correct. I even changed it to a local directory on "C" to make sure it was not a server issue.
Filename shows as "c:\test\183OfficeReport.p
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim FileName As String
Set db = CurrentDb()
Set qd = db.QueryDefs!qUniqueOffices
Set rs = qd.OpenRecordset
Do Until rs.EOF = True
Me.txtHiddenOffice = rs!repOffice
FileName = "c:\test\" & rs!repOffice & "Report.pdf"
'& rs!repMetro & "\Office Reports\"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, FileName, False
rs.MoveNext
Loop
rs.Close
qd.CloseFilename shows as "c:\test\183OfficeReport.p
You are missing the report name
DoCmd.OutputTo acOutputReport, "reportname", acFormatPDF, FileName, False
DoCmd.OutputTo acOutputReport, "reportname", acFormatPDF, FileName, False
ASKER
Perfect!!!! You are the man (or Woman)!!!!!!
ASKER
Pat was great! Thanks for the all of the help.
You're welcome.
ASKER