automate pdf report printing

[ronwmon]

I have an Access database that tracks services purchased by customers. I have a report that we use for billing that shows each customer with the details of the services purchased. The report totals the customer purchases. We use this report for billing.

The report paginates by customer. Currently we manually select pages associated with a customer and print them to a PDF printer in order to email a report to a customer. The report separates the customers by customer number.

I would like to be able to automatically print these PDF reports for each customer. It would save a lot of time.

Any ideas are appreciated, thanks in advane.

Ron Wies

 

[MajP]

Do you want to Print and send the email automatically, or print and save to a file then email maually. Both can be done. Also in newer versions of Access this is much easier. I think it is 2007 (maybe 2010) where saving as PDF became very easy. What version of Access are you?

Bottom line since the report paginates by customer, you should be able to write a query that returns a list of those customers. You then open a recordset on that list. Loop the recordset and then either save the individual PDF to file or if you have the email addresses stored you send the email with a PDF attachment.

 

[ronwmon]

MajP,

Thanks for your response. I have been very busy. I have never worked with the "Recordset" before. I have done a little research and written some code. Below is the code I have written. If I enter the DealerID in the "Copy Of AlarmNetBillingCalcQry" query I can make the function work. I then added code and tried to have the code update the query with the DealerID. I can't make this work.

Any help you can provide is appreciated.

Ron--

Private Sub Command18_Click()
On Error GoTo Err_Command18_Click

Dim stDocName As String
Dim rstObj As DAO.Recordset
Dim msgStr As String
Dim NamStr As String
Dim DlNum As String

Set rstObj = CurrentDb.OpenRecordset("Dealer_List")

Do While Not rstObj.EOF
msgStr = rstObj.Fields("DealerID") & vbCrLf
DlNum = rstObj.Fields("DealerID")
Set [Copy Of DealerAlarmNetBillingCalcQry].[DealerID] = DlNum
Set NamStr = ([Copy Of DealerAlarmNetBillingCalcQry].[Company Name])
rstObj.MoveNext
Loop

stDocName = "NewDealer"
DoCmd.OpenReport stDocName, acNormal
DoCmd.OutputTo acOutputReport, "NewDealer", acFormatPDF, "d:\ronfls\" & NamStr & ".PDF"
'MsgBox (msgStr)
'Loop
Set rstObj = Nothing

Exit_Command18_Click:
Exit Sub

Err_Command18_Click:
MsgBox Err.Description
Resume Exit_Command18_Click

End Sub

 

[MajP]

I just showed my generic code in another post asking the exact same question

Public Sub ExportToPDF()
  Const Folder = "C:\"
  Const Domain = "qryBudgetNew"
  'Domain can be table name, query name, or sql statement that provides the values to loop
  Const LoopedField = "departmentID"
  Const ReportName = "rptBudgetCombined"
  
  Dim rs As DAO.Recordset
  Dim LoopedFieldValue As Long
  Dim FileName As String
  Dim FullPath As String
  Dim strWhere As String
  Set rs = CurrentDb.OpenRecordset(Domain)
 
  Do While Not rs.EOF
    LoopedFieldValue = rs.Fields(LoopedField)
    FileName = LoopedFieldValue & ".PDF"
    'The field may be a text field. It then must be surrounded in singlequotes. If so uncomment below
    'LoopedFieldValue = "'" & LoopedFieldValue & "'"
    FullPath = Folder & FileName
    strWhere = LoopedField & " = " & LoopedFieldValue
    Debug.Print FullPath
    Debug.Print strWhere
    DoCmd.OpenReport ReportName, acViewPreview, , strWhere
    DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath
    DoCmd.Close acReport, ReportName
    rs.MoveNext
  Loop
End Sub

to adapt to you here are the changes. It appears you want your file name to include the company name.

Public Sub ExportToPDF()
  Const Folder = "d:\ronfls\"
  Const Domain = "Dealer_List"
  'Domain can be table name, query name, or sql statement that provides the values to loop
  Const LoopedField = "DealerID"
  Const ReportName = "NewDealer"
  
  Dim rs As DAO.Recordset
  Dim LoopedFieldValue As Long
  Dim FileName As String
  Dim FullPath As String
  Dim strWhere As String
  Dim CompanyName as string

  Set rs = CurrentDb.OpenRecordset(Domain)
  Do While Not rs.EOF
    LoopedFieldValue = rs.Fields(LoopedField)
    'If the company name is in Dealer_List then
    CompanyName = rs.fields("[Company Name]")
    FileName = CompanyName & ".PDF"
    'The field may be a text field. It then must be surrounded in singlequotes. If so uncomment below
    'LoopedFieldValue = "'" & LoopedFieldValue & "'"
    FullPath = Folder & FileName
    strWhere = LoopedField & " = " & LoopedFieldValue
    'verify these
    Debug.Print FullPath
    Debug.Print strWhere
    DoCmd.OpenReport ReportName, acViewPreview, , strWhere
    DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath
    DoCmd.Close acReport, ReportName
    rs.MoveNext
  Loop
End Sub

 

[ronwmon]

Majp,

When I run the code, I get a Compile error: Object required.

[highlight #729FCF]NamStr =[/highlight] is highlighted in the line - - Set NamStr = ([Copy Of DealerAlarmNetBillingCalcQry].[Company Name])

Your assistance is appreciated.

Ron--

 

[dhookom]

You might look at thread705-1738933.

Your line of code should not use "Set" to update a string or numeric variable.

Change:

Set NamStr = ([Copy Of DealerAlarmNetBillingCalcQry].[Company Name])

To:

NamStr = ([Copy Of DealerAlarmNetBillingCalcQry].[Company Name])

I find it hard to believe the code works since "[Copy Of DealerAlarmNetBillingCalcQry]" should be out of scope.

Duane
Hook'D on Access
MS Access MVP

 

[ronwmon]

dhookom,

I failed to copy the entire line. It does have the set command at the beginning.

[Copy of DealerAlarmNetBillingCalcQry] is a query that has a field titled [Company Name].

How should that be coded? We need to make NamStr = the field [Company Name] from the query.

Ron==

 

[dhookom]

A query can have 0 to thousands of records. Which record has the value you need? You can possibly use DLookup() to get the value of any fields in a table or query.

Again, you don't use "SET" when assigning a value to a string or numeric variable.

Duane
Hook'D on Access
MS Access MVP

 

[ronwmon]

dhookom,

The code we are writing looks at each dealers account by dealer number. It then creates a report based on that dealers history. I want to put the dealers name on the report, that is the reason for extracting the name.

Ron--

 

[dhookom]

Isn't the information you need in Dealer_List?

Duane
Hook'D on Access
MS Access MVP

 

[ronwmon]

dhookom,

The query Dealer_List is just the dealer numbers sorted in order.

The query code is:

SELECT DISTINCT ([MoAlarmNetBilling].[DealerID]) AS DealerID
FROM MoAlarmNetBilling;

The dealer name does not exist in MoAlarmNetBilling.

MoAlarmNetBilling is a collection of all of the records form our wupplier AlarmNet for the month. It only refers to the dealer number. We attach the dealer name in other queries to print on reports.

Ron--

 

[dhookom]

Again, "A query can have 0 to thousands of records. Which record has the value you need?"

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

So make a query that shows the distinct ID and names.

DealerList:
SELECT DISTINCT ([MoAlarmNetBilling].[DealerID]) AS DealerID, .... as DealerName
FROM MoAlarmNetBilling .... inner join to get the dealerName

Use that query to loop as I show. I think all the other code is already there. I would stop trying to fix your code, because it has a lot of problems,