Creating multiple pdf files from one Access report

[ronwmon]

We do business with a company that provides a service to many of our customers. I have an Access database that I use to import the .csv file I get from the service company. The .csv file has the details of each item and the company associated with the service.

I have a report that prints the details for each customer, the service and the price for the service. This report is about 100 pages and services about 60 of our customers.

I need to be able to create a .pdf file for each of my customers from the report I have set up. I would be willing to create a new report if necessary to do the .pdf process. I need to be able to name the .pdf with the company name and todays date.

Any help is appreciated.

 

[ronwmon]

That is exactly how I have it. When I run the code I get:

Compile error:

Syntax error

Ron--

 

[MajP]

Can you cut and paste your code? And show where it errors.

 

[ronwmon]

When I try to run the code I get the error:

Compile error:

Syntax error

When I click OK on the error message box the line highlighted in yellow is then highlighted.

The line with the red text is red all the time after I put the single quotes in.

If I remove the single quotes the line is black. When I put the single quotes in the line I get error:

Compile error:
Expected: end of statement

The text then turns red.

ROn--

 

[MajP]

Not sure if that is an issue with the PDF file or if you really combined two lines. If you really combined two lines you will get that error

DoCmd.OpenReport rptName, acViewPreview, , "[Company Name] = '"& companyName &"'" companyname = Replace(companyname, " ","_")

That has to be two seperate lines

1 DoCmd.OpenReport rptName, acViewPreview, , "[Company Name] = '"& companyName &"'" 
2 companyname = Replace(companyname, " ","_")

 

[ronwmon]

I can assure you I have it entered as two lines.

I copied your first line and pasted it in my code. As soon as i did so I got the compile error I refferenced in the last message.

In Access 2010 I have 4 choices in the Macros & Code section. They are Macro, Module, Class Module and Visual Basic. I been using the Visual Basic selection. Is this correct?

Ron-

 

[MajP]

Could you please post the code directly here and not the PDF? use the formatting tags for code. Pick the icon to the left of the yellow box with a red bow on it. If it is a syntax error I can then post it into vba to see where the error is.

 

[ronwmon]

Public Sub LoopCustomers()
Dim rs As DAO.Recordset
Dim strSql As String
Dim companyName As String
Const qryName = " DealerAlarmNetBillingCalcQry"
Const fieldName = "[Company Name]"

strSql = "SELECT DISTINCT " & fieldName & " FROM " & qryName
Debug.Print strSql
Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF
companyName = rs.Fields(fieldName)
CreateCustomerReports (companyName)
rs.MoveNext
Loop

End Sub

Public Sub CreateCustomerReports(companyName As String)
Const rptName = "Monthly Dealer AlarmNet Billing"
Const strPath = "\\database\alarmnet\dealerinvoice"
DoCmd.OpenReport rptName, acViewPreview, , "[Company Name] = '"& companyName &"'" companyname = Replace(companyname, " ","_")
DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, strPath & companyName & ".pdf"
End Sub

 

[MajP]

I can assure you I have it entered as two lines

I am not sure what to say, but that sure looks like you entered it as one line of code not two.

DoCmd.OpenReport rptName, acViewPreview, , "[Company Name] = '"& companyName &"'" companyname = Replace(companyname, " ","_")

It has to be

DoCmd.OpenReport rptName, acViewPreview, , "[Company Name] = '"& companyName &"'" 
companyname = Replace(companyname, " ","_")

 

[ronwmon]

Sorry, I didn't realize what you were referring to regarding 2 lines. I did have that as 1 line. Now it is 2 and it appears to be progress.

When I run the code it seems to run but errors out with:

Ron-time error '2501'
The Output To action was canceled.

Wne it runs I see a window:

Now outputting 'AlarmNet Billing4'
to the file 'r:database\alarmnet\dealerinvoiceAccurtech,_LLC'.
Current page 81
Cancel Button

The Current page counts up to 81. This is the last page of the report but errors without saving the .pdf lile.

I really want 48 reports from the 81 pages of data, not one report.

I may be wrong but it appears this code is trying to write one report from the data.

 

[MajP]

post your code. Looks to me like you did something wrong and dropped the ".pdf" from the name concatenation.

 

[ronwmon]

Here is the code:

Public Sub LoopCustomers()
Dim rs As DAO.Recordset
Dim strSql As String
Dim companyName As String
Const qryName = " DealerAlarmNetBillingCalcQry"
Const fieldName = "[Company Name]"

strSql = "SELECT DISTINCT " & fieldName & " FROM " & qryName
Debug.Print strSql
Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF
companyName = rs.Fields(fieldName)
CreateCustomerReports (companyName)
rs.MoveNext
Loop

End Sub

Public Sub CreateCustomerReports(companyName As String)
Const rptName = "Monthly Dealer AlarmNet Billing"
Const strPath = "r:\database\alarmnet\dealerinvoice"
DoCmd.OpenReport rptName, acViewPreview, , "[Company Name] = '" & companyName & "'"
companyName = Replace(companyName, " ", "_")
MsgBox (rptName)
MsgBox (companyName)
MsgBox (strPath)
DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, strPath & companyName '& ".pdf"
End Sub

 

[MajP]

So I post this code

DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, strPath & companyName & ".pdf"

But you change it to this

DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, strPath & companyName '& ".pdf"

Why? Trust me, my code is almost always correct. If not I will specify untested.

 

[dhookom]

Consider a solution where you change the SQL property of the query that is the record source of the report prior to sending to PDF

Public Sub LoopCustomers()
	Dim rs As DAO.Recordset
	Dim db As DAO.Database
	Dim qd as DAO.QueryDef
	Dim strRptQueryName as String   'name of the query that is the record source of rptName
	
	Dim strSql As String
	Dim strSQLRptBase as String
	strSQLRptBase = "SELECT * FROM SomeQueryName"

	Dim companyName As String
	Const qryName = "DealerAlarmNetBillingCalcQry"
	Const fieldName = "[Company Name]"
        strRptQueryName = "Your Report Recordsource Query Name Here"
	strSql = "SELECT DISTINCT " & fieldName & " FROM " & qryName
	Debug.Print strSql
	Set db = CurrentDb
	Set rs = db.OpenRecordset(strSql)
	Do While Not rs.EOF
		companyName = rs.Fields(fieldName)
		db.QueryDefs(strRptQueryName).SQL =  strSQLRptBase & " WHERE [Company Name]='" & companyName & "'"
		CreateCustomerReports (companyName)
		rs.MoveNext
	Loop
	Set qd = nothing
	rs.Close
	Set rs = Nothing
	Set db = Nothing

End Sub

Public Sub CreateCustomerReports(companyName As String)
	Const rptName = "Monthly Dealer AlarmNet Billing"
	Const strPath = "r:\database\alarmnet\dealerinvoice"
	DoCmd.OpenReport rptName, acViewPreview, , "[Company Name] = '" & companyName & "'"
	companyName = Replace(companyName, " ", "_")
	MsgBox (rptName)
	MsgBox (companyName)
	MsgBox (strPath)
	DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, strPath & companyName & ".pdf"
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

SQL property of the query that is the record source of the report prior to sending to PDF

1. Why? that is overkill IMO
2. However, I do see the problem in this version of the code. You simply need to close each report before opening the next.
3. If you were going to change the sql then no need to open to the specific company in this line
DoCmd.OpenReport rptName, acViewPreview, , "[Company Name] = '" & companyName & "'"

I can assure you this saves individual reports for each customer.

Public Sub CreateCustomerReports(companyName As String)
  Const rptName = "rptCustomers"
  Const strPath = "C:\"
  Const fieldName = "Company Name"
  Dim pathAndFile As String
  companyName = Replace(companyName, "'", "''")
  DoCmd.OpenReport rptName, acViewPreview, , "[" & fieldName & "] = '" & companyName & "'"
  companyName = Replace(companyName, " ", "_")
  pathAndFile = strPath & companyName & ".pdf"
  Debug.Print pathAndFile
  DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, pathAndFile
  'add here
  [b]DoCmd.Close acReport, rptName [/b]
End Sub

 

[dhookom]

Good point MajP. I was coming in a bit late. I typically don't open and then OutputTo. I just send to PDF.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I guess I should have said it is overkill to do both. Either change the sql and output as you suggest, or open the report to the specific record and output.

 

[MacroScope]

You're getting the errors because you didn't change the query and report names completely.