Automatically split a pdf report according to customer name and region

[Fuzz_cozens]

Morning,

I have a report called "Brokerage Commission Statement" in my access 2010 DB. The query (recordsource) linked to this report is called "Commission Statement Query"

Currently the report consists of about 700 pages for all of the 184 brokerages I have on my books so currently I am manually saving each part of the PDF 184 times which takes me about 4 hours every 3rd day of the week.

I have been reading on the forums that by using VB code this process could be automated using DoCmd.OutputTo acReport however I need urgent help with this code as I have really tried for a few weeks now and am also not 100% sure which event I should put this code under.



I would like the report to be split to pdf according to a field in my query called [Brokerage] and then saved to a folder on my desktop.

Please any help or advice would be appreciated

Richard

[June7]

Review https://www.accessforums.net/import-...ges-25042.html

The code can be in a button Click event.

When you have issue about specific code for your requirements, post question with the attempted code.

[Fuzz_cozens]

Hi June,

Thanks for your reply and I do understand exactly what you are saying. I have never done this and I merely read that this could save me a lot of time every 3 days.

I created a button and put in the following code:

Private Sub Command92_Click()
Dim MyPath As String
Dim MyFilename As String
MyPath = "C:\Stats Reports\"
'Loop structure may vary depending on how you obtain values
For Each uniqueId In uniqueIds
MyFilename = "Commission Statement - 1Life Agent Summary" & uniqueId & ".pdf"
'Open report preview and auto-save it as a PDF
DoCmd.OpenReport "brokerage commission statement - 1Life", acViewPreview, , uniqueField = "brokerage"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False
'Close the previewed report
DoCmd.Close acReport, "brokerage commission statement - 1Life"
Next uniqueId
End Sub

Please can someone help me with this

Thanks

Richard

[June7]

Your code shows looping through something called uniqueIDs but does not show uniqueIDs declared as a variable. So what is uniqueIDs?

Doesn't look like you took advantage of the example in the link I referenced.

You do not show declaring and opening a recordset. Adapt the example to your database.

[Fuzz_cozens]

Hi Wendy

I will try to adapt your example into my database and repost. Thanks again for your assistance

[Fuzz_cozens]

Hi Wendy

See code below for you to have a look at:

Private Sub Command92_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "Commission Statement - 1Life Agent Summary;", cn, adOpenStatic, adLockPessimistic
While Not rs.EOF
DoCmd.OpenReport "brokerage commission statement - 1Life", acViewPreview, , "LabNum='" & rs!LABNUM & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, "C:\Stats Report\" & rs!brokerage & ".pdf", False
DoCmd.Close acReport, "brokerage commission statement - 1Life", acSaveNo
rs.MoveNext
Wend


I am getting an error "User defined type not defined" for the 2nd line of code which is "Dim cn As ADODB.Connection" I am not sure what this is. Please can you check the rest of the code for me bearing in mind the report name is "Brokerage Commission Statements - 1Life" and the query or recordset is called "Commission Statement - 1Life Agent Summary". The field within the query as the identifier is called "Brokerage"

Thanks in advance

Richard

[June7]

In the VBA editor > Tools > References, need to set a VBA reference for Microsoft ActiveX Data Objects 6.1 Library.

Also, need an SQL statement for the rs.Open (note the [] surrounding the query name because it contains spaces and punctuation):

rs.Open "SELECT Brokerage FROM [Commission Statement - 1Life Agent Summary];", cn, adOpenStatic, adLockPessimistic

I doubt LabNum is relevant to your database. Use your field names in the OpenReport argument: "Brokerage='" & rs!Brokerage & "'"

Is Brokerage a text field?

[Fuzz_cozens]

Thanks again June. Will do. Yes the Brokerage name is a text field