INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

automate pdf report printing

automate pdf report printing

(OP)
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

RE: automate pdf report printing

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.

RE: automate pdf report printing

(OP)
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

RE: automate pdf report printing

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

CODE

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.

CODE

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 

RE: automate pdf report printing

(OP)
Majp,

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

NamStr = is highlighted in the line - - Set NamStr = ([Copy Of DealerAlarmNetBillingCalcQry].[Company Name])

Your assistance is appreciated.

Ron--

RE: automate pdf report printing

You might look at thread705-1738933: I have a report that I group on a field. I want to the report split into individual PDFs for each..

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

Change:

CODE --> vba

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

CODE --> vba

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

RE: automate pdf report printing

(OP)
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==

RE: automate pdf report printing

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

RE: automate pdf report printing

(OP)
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--

RE: automate pdf report printing

(OP)
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--

RE: automate pdf report printing

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

RE: automate pdf report printing

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

CODE

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,

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close