Macro to generate distinct reports based on different records

[corai]

Hi All,

I am trying to create a macro to export a report to pdf multiple times for a whole series of different records. The report (rptQryS) is essentially a template which contains different information depending on which record of a form (frmCompany) is selected.

I've had a go with the macro builder but haven't got very far as I'm new to all of this.

This is what I have so far:

'------------------------------------------------------------
' ReportGenerationMacro
'
'------------------------------------------------------------
Function ReportGenerationMacro()
On Error GoTo ReportGenerationMacro_Err
DoCmd.OpenForm "frmCOMPANY", acNormal, "", "", , acNormal
DoCmd.Save acForm, "frmCOMPANY"
DoCmd.RunMacro "Repeat", 3, ""

ReportGenerationMacro_Exit:
Exit Function
ReportGenerationMacro_Err:
MsgBox Error$
Resume ReportGenerationMacro_Exit
End Function

'------------------------------------------------------------
' ReportGenerationMacro_Repeat
'
'------------------------------------------------------------
Function ReportGenerationMacro_Repeat()
On Error GoTo ReportGenerationMacro_Repeat_Err
DoCmd.OutputTo acOutputReport, "rptQryS", "PDFFormat(*.pdf)", "\\Computer\x\y\z\REPORT\" & Forms!frmCompany!TradingName & ".pdf", False, "", , acExportQualityPrint
DoCmd.OpenForm "frmCOMPANY", acNormal, "", "", , acNormal
DoCmd.SearchForRecord acForm, "frmCOMPANY", acNext, ""
DoCmd.Save acForm, "frmCOMPANY"
DoCmd.Requery ""

ReportGenerationMacro_Repeat_Exit:
Exit Function
ReportGenerationMacro_Repeat_Err:
MsgBox Error$
Resume ReportGenerationMacro_Repeat_Exit
End Function

​

This is currently giving me an error - "an expression argument 3 has an invalid value". Does anyone have any advice or perhaps there is a better way of doing this?

Many thanks in advance.

 

[brockton]

Wish I could help... I am also new to all of this. I will be watching this to see a good answer on this situation.

 

[PNGBill]

I suggest you use vba code to perform this task.

Your situation sounds similar to running monthly Statements for all customers.
ie One Report but you need it produced for each customer.

This is a perfect Loop situation.

You decide what the criteria is for the report. All Customers, or just those with Positive balances.
This is done by a query.

Once you are satisfied with the correct records being returned eg 73 customer acc numbers, then you create your vba code using this query (sql) as the Recordset.
The Loop will run through this recordset (73 records) and when it gets to each, it will produce the Statement.
The code can save the report for archives and or printing or even email the report.
Then onto the next record.

This vba code will be in the OnClick event of your Comand Button and each time you Click the button, Statements wil be produced for all accounts that match the criteria.

You can have Input Boxes in the code to verify you want to run the task (didn't click by accident) and even to check the date or if the task has already been done etc.

There are plenty of examples of Loop vba code and it is not as hard it may seem.

VBA also is one of the most visited forums here for advice.

 

[corai]

Thanks PNGBill. This is a good solution.

I started off with my report (rptQryS) linked to a form and used the following code for mass pdf report generation:

Do
DoCmd.OpenReport "rptQryS", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "rptQryS", "PDFFormat(*.pdf)", "\PROJECTS\\PROGRAMMING\REPORT\" & Forms!frmCompany!TradingName & ".pdf", False, "", , acExportQualityPrint
DoCmd.Close acReport, "rptQryS"

DoCmd.SelectObject acForm, "frmCOMPANY", False
DoCmd.GoToRecord acForm, "frmCOMPANY", acNext
DoCmd.Save acForm, "frmCOMPANY"

Loop

This works but eventually an error message comes up saying "Cannot Open Any More Tables". For this reason I would suggest this solution for ad hoc reporting whereby users can use a form to select a company and enter criteria and use a button to generate one off reports.

For mass report generation it is better to have the report linked to a query as PNGBill suggest. Here is some code I used:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
Dim filterDefintion As String
Dim filename As String

strSQL = "Select * FROM qryReportInfo "

Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst

Do While rs.EOF = False
filterDefinition = "[Company ID] = " & rs![Company ID]
filename = rs![TradingName] & rs![Company ID]
filename = Replace(filename, "/", "-")
DoCmd.OpenReport "LETTER", acViewReport, "qryReportInfo", filterDefinition, acNormal
DoCmd.OutputTo acOutputReport, "LETTER", "PDFFormat(*.pdf)", "\\PROJECTS\PROGRAMMING\REPORT\LETTER\" & filename & ".pdf", False, "", , acExportQualityPrint
DoCmd.Close acReport, "LETTER"


rs.MoveNext

Loop

...note that vba does not appreciate the "/" character

 

[aparnag]

Hi ,

I am trying to do exactly the same thing. I want to create a report for each lender detailing all there loans.I have query which pulls all the records including lender name. So have few questions.
Q1: Do you think I can use your vba code and replace the query name to my query name and replace company id with officer name. Will it work?

Q2: Where do write this procedure? i.e. in the report load procedure or some where else??

Appreciate your help.

Thanks,
Aparna

 

[corai]

Hi Aparna,

Yes you should be able to use this code although you will have to amend all the references to names which I have used for my DB. You won't necessarily need the "filterdefinition" line of code either...

Write the vba code in a module and call it from a button on your form.

 

[aparnag]

Thanks,

I went ahead put the code in a module and called in the onclick event, but it just does not do anything.
May be I think my query is not formatted correctly. The query is
is grouped by account number and contains details including lender name.
But do u think it is because it is not grouped by lender, it is not working.
I can not group it by lender as I want to report all the loans linked to the lender.
That is what my report in detail section has acc-no, and all the details and is grouped by lender.

I hope I am not going in circles.
:banghead:

Appreciate your help,
Aparna