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.

 

[MacroScope]

Is it the same report for each customer with only the header changed, or does the report content vary with customers as well?

 

[ronwmon]

The report content varies. One customer may have 2 tiems on their report another may have 100.

Ron--

 

[MacroScope]

Sorry for the 20 questions. I'm trying to understand exactly what you want and what you have before offering suggestions. You want to send the unique .pdf to each customer via email, correct?

What's the name of the table which contains the data that you want to send? Is that the same table that contains customer information such as name, email address, and anything else that you need to personalize it, or is that in another table linked by an ID number?

You say you already have a report that contains the information you want to send. Is it already personalized to each customer? If so, how do you select which customer will be on that report?

 

[ronwmon]

20 questions are fine. I want to create pdf files for each customer, most will be emailed.

My database has a table titled MoAlarmNetBilling. There are 2 tables used in the report. They are DealerAlarmNetCalcQry and DealerAlarNetBillingOper Query.

I have attached design views of the two queries, two print previews of reports and the report.

The report looks at DealerAlarmNetCalcQry. DealerAlarmNetCalcQry looks at DealerAlarmNetBillingOper Query. DealerAlarmNetBillingOper Query looks at the table.

Some of the reports are as simple as Report2, others have hundreds of records and use multiple pages.

The data exists on multile files. The table moAlarmNetBilling contains the months data including the dealer number. The table Companies contains the dealer number and dealer name. DealerAlarmNetBillingOper Query extracts data from moAlarmNetBilling and creates two fields, Service and Overage and totals those fields. DealerAlarmNetCalcQry gets the dealer name from the Companies table and does math on two fields, Service & Overage.

As you can see, the reort is personalized.

Ron--

 

[MacroScope]

I'm sure you thought I'd never answer, but I had some delays in getting to this, took time to create and test the routine, and I also had some issues with Outlook on my computer that took some time.

I created quite a simple routine that works just fine for me. It's going to take a lot more time and effort to explain than to implement, I think.

First off, in the query that is the source for your report you need an additional Yes/No field. Call it Sent for simplicity. Its function eventually is to confirm which emails have been sent and which have not yet been sent. Looking at your query structure, I'd probably put it in the Companies table and then bring it in. You'll also need to bring in the email address field into the query, and eventually into the report. Sent does not have to be on the report, but the email address should be. Make it invisible so it doesn't mess with your visuals that you've already got, but it needs to be there.

You'll also need to create an update query which will update the Sent field back to all False again after you've sent all the emails out. Click Create>Query Design, select the Companies table, and in Design View click Update. Drag the Sent field down, and in the Update To field write False. I called my Query UpdateSent, but call it whatever works for you.

Now you'll need a trigger, be it a Command Button or some other method, but that trigger will run a macro. I'm going to assume a button, and you can modify accordingly. Call the button Command16, and the macro Control, with several named macros within it.

I called the first named macro RunReports, so your trigger event should run Control.RunReports. The RunReports macro has one step in it, a RunMacro command. It will have its RepeatCount condition set to =DCount("[CompanyName]","[DealerAlarmNetBillingCalcQry]") Essentially it will count how many names are in the list and that's how many times it will run. The name of the macro it runs repeatedly is the second named macro in the Control macro group that I simply called Send. It will be named Control.Send in the MacroName field.

Now create the Send macro with the following steps and conditions

Echo>No (keeps operations from being visible on screen until it's all done)
OpenQuery>DealerAlarmNetBillingCalcQry (Opens query so operations can be performed on data it contains)
GoToControl>Sent (Takes the cursor to the Sent field)
FindRecord>=False (This simply searches through the sent field until it lands on a record that has not yet been sent
SetValue>Screen.ActiveControl>True Sets that false record to True)
GoToControl>CompanyName. (This is the identifier that will tell Access which customer's report to print, on the same record you just set to True)
SetTempVar>CompanyName>Screen.ActiveControl (Sets the company name of the same record as a temporary variable to direct the send)
Close>Query>DealerAlarmNetBillingCalcQry (Closes query after you're finished writing to it and you've recorded the company name)
OpenReport>Monthly Dealer AlarmNet Billing>Print Preview. In Where Condition, write [Company Name]=[TempVars]![CompanyName]. You're now just opening the report with the one record of interest.
SendObject>Report>Monthly Dealer AlarmNet Billing>PDF Format. In To: field, put =Screen.ActiveReport!Email, or =[Reports]![Monthly Dealer AlarmNet Billing]!. You'll definitely need square brackets around that report name because it contains spaces. Fill in Subject and Message text as desired, and edit message to NO. That allows for immediate send.

Now the last step is to create a condition in the condition field of the same macro, DCount("[Sent]","[DealerAlarmNetBillingCalcQry]","[Sent]=False")=0. Basically this and succeeding operations will only run after all the check boxes are set to True, meaning all the records have been sent.
In the Action column in front of that condition, put
SetWarnings>No (This disables warnings when you run the update query)
Under the condition expression put an ellipses (...) which indicates that this step will also run when condition is met. In the action column put:
OpenQuery>UpdateSent (This will go back and change all the true values to false ready for the next send)

And you're done in 12 steps.

One click runs the Send macro as many times as there are records to send. It finds a record that hasn't been sent yet, labels it as sent, saves the company name for that record, then opens the report for only that name, attaches it to an email, and sends it. It then repeats the same steps for each record until all are sent. The last step, performed only after they're all sent, resets all the values to false for the next time you want to perform the operation.

I was having problems with Outlook and I wound up with all the records in the outbox, attached and ready to send, but I had to manually release them from there. If your Outlook is working correctly you should be able to send all of them individually with the one click.

Good luck. Let me know. I'm sure there are other ways to accomplish this, but it is simple and it works well.

 

[ronwmon]

Thank you

I am out of town, in Salt Lake City. I will return next Thursday. I won't be able to try it until then.

Ron--

 

[MacroScope]

Just FYI, I think you should take the two steps at the end that are conditional and remove the condition and just add them under the RunMacro command in the Control.RunReports macro. Basically the update will just run after the Send macro has run the number of times needed to send all records. Don't need to do a comparison. After it's done sending it just sets all the Sent controls back to False, ready for the next time. Cleaner and easier that way.

 

[MacroScope]

One other thing. I chose to use Company Name rather than CompanyID because the ID field did not seem to be available on the report, but a unique number is always better than a name as an identifier. If you add the ID number to the report, even if invisible, then all the steps I outlined that refer to Company Name will just be changed to CompanyID.

 

[MajP]

Loop your customer names, then open and save a report for each customer. You can easily modify to also send the emails.

Public Sub LoopCustomers()
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim companyName As Long
  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 = "YourReportNameHere"
  Const strPath = "YourFilePathHere"
  DoCmd.OpenReport rptName, acViewPreview, , "[Company Name] = " & companyName
  DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, strPath & companyName & ".pdf"
End Sub

 

[ronwmon]

Thanks for your efforts. I began to enter your code today but ran into a problem.

I don't want to sound like an idiot but I haven't written any macros in Access. I am running Office 2010, my version of Access has a "Create" tab that has a few options under Macros and Code. One choice is simply Macro but when I click this I get an "Add New Action" list box. There is a choice for Module, Class Module and Visual Basic.

When I use any of the last 3 as soon as I enter Echo>No I get a message saying Compile error Expected:expression.

How should I be enterning the code?

Thanks,

Ron--

 

[MacroScope]

Right. Access 2010 changed their macro structure completely. It's much more complex and much less intuitive than it was. I created this in 2007. I'll have to play with it in 2010 to create the same result.

 

[MacroScope]

I simply opened the Access 2007 db in 2010 and then saved the macro in that format. Here it is. Save this file to your hard drive, then open Access and use the import to import the macro from this db to your current database. You'll need to change report and query names to match your own, but the steps work fine.

 

[ronwmon]

I made changes to the report & query names. The attached file is the file you sent me with the changes in Control-2. I get a 2950 error when I try to run the macro. I searched Google and found several articles regarding error 2950. I set the trust options to our shared database folder (\\database\).

I replaced the name of the first item with our report name. The third itme which is a query I put the name of the query the report looks to. This query looks to another query whos name is in the 5th area and replaced your name "Counties".

The file attached is the macro after my changes.

Ron--

 

[MajP]

Or you could just use the code I provided and be done.

 

[MacroScope]

Just change all the names to correspond with the names in your db.

 

[ronwmon]

MajP I tried your suggestion. I copied and pasted your code in the "Visual Basic" macro in Access 2010. In the "CreateCustomerReports" sub I set the rptName to "Monthly Dealer AlarmNet Billing" and strPath to "\\database\alarmnet\dealerinvoice".

When I ran the macro I got error 13. I changed Dim company name to string. You had it set as long.

Now when I run the code I get error 3075, Syntax error (comma) in query xpression '[Company Name] = Accurtech, LLC'. In the results section below the code section it appears it succeded 5 times before the error. I get:

SELECT DISTINCT [Company Name] FROM DealerAlarmNetBillingCalcQry
SELECT DISTINCT [Company Name] FROM DealerAlarmNetBillingCalcQry
SELECT DISTINCT [Company Name] FROM DealerAlarmNetBillingCalcQry
SELECT DISTINCT [Company Name] FROM DealerAlarmNetBillingCalcQry
SELECT DISTINCT [Company Name] FROM DealerAlarmNetBillingCalcQry

Any ideas?

Thanks

 

[MajP]

Sorry I tested it using a long field and did not make the changes before posting
So you would need someting to surround the text with single quotes.

DoCmd.OpenReport rptName, acViewPreview, , "[Company Name] = '" & companyName & "'"
companyName = Replace(companyName, " ", "_")
DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, strPath & companyName & ".pdf"

Also you cannot save a file name with a space so I replaced the space with an underscore before saving.

 

[ronwmon]

I can't seem to figure out where to put the single quote. In one place it starts a comment, in other places I get a Compile and Syntax error.

Ron--

 

[MajP]

There should be two single quotes
"[Company Name] = [red]'[/red]" & companyName & "[red]'[/red]