Create multiple reports using macros

[Access User]

There is a report that I would like to run monthly for about 90
individuals and I want each individual to get their own report.
Clearly, I don't want to run the report 90 times. I assume that I
need a macro to do this, but have no experience writing macros.
Currently, I have set up a make table query to identify the users that
will need the report (since that list is subject to change) and the
report is set up with the user as a parameter, now I just need to
connect the two! Any assistance would be greatly appreciated.

Thanks,

Kevin

 

[Duane Hookom]

Do you actually need 90 separate reports or can you get by with a single
report with individuals each beginning on a new page?

If you actually want multiple reports, I would write some DAO code that
would open a recordset of the 90 employees and step through running the
reports. The following is "air" code and makes a lot of assumptions regarding
table, field, and report names.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strWhere As String
Set db = CurrentDb
set rs = db.OpenRecordset("tblMadeTable")
With rs
.movefirst
Do Until .eof
strWhere = "IndividualID = " & !IndividualID
DoCmd.OpenReport "rptMonthlyInd" , acNormal, , strWhere
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing

 

[Access User]

Thanks. Unfortunately, I'm not having any luck. Like I said, I'm
pretty novice at writing code, can you hightlight/underline the parts
of code that are table, field or report names? I just want to be sure
I'm applying this code to my app properly.

 

[Duane Hookom]

table name: tblMadeTable
Field Name: IndividualID
Report Name: rptMonthlyInd

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strWhere As String
Set db = CurrentDb
set rs = db.OpenRecordset("tblMadeTable")
With rs
.movefirst
Do Until .eof
strWhere = "IndividualID = " & !IndividualID
DoCmd.OpenReport "rptMonthlyInd" , acNormal, , strWhere
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing

 

[Access User]

I can't seem to get this to run. I think it may have something to do
with the fact that the underlying query for the report is a crosstab
query, which means that I have to enter parameter in the query. If I
leave the parameters in the crosstab query, when I run the macro, it
asks for the parameters, if I remove the parameters from the query, it
tells me that it doesn't recognize that parameter name even though I
have matched up the field names in the macro with the parameter
name... what am I missing?
Also, it appears to be trying to send the reports directly to the
printer. I don't see anything in the coding that would indicate that
we want that to happen, but I definitely want these reports sent to a
file rather than sent to the printer.

 

[Duane Hookom]

"I don't see anything in the " & [initial posting] & " that would indicate:"
-the record source is a crosstab query
-I definitely want these reports sent to a file

How about providing the report record source SQL as well as some other
significant information?

 

[Access User]

I apologize, I didn't realize that that was significant or would
change the macro. I was more or less looking for a "shell" of a macro
rather than have you write the actual macro for me. I attached the
sql statement from the underlying query. The parameters that I enter
into the query are the submitter (strsubmitter), then a particular
month. This data now exists in a table (tblsubmitters) through a make
table query.

SELECT qryCheck_Resources.Submitter, tblResource.Name,
qryCheck_ActualsVsForecasts_Crosstab.Category,
qryCheck_ActualsVsForecasts_Crosstab.Activity,
MonthName(Month([Month]),False) AS MonthName, Year([Month]) AS [Year],
nz([Actuals],0) AS ActualHours, nz([Forecast],0) AS FcstHours
FROM (tblResource INNER JOIN qryCheck_ActualsVsForecasts_Crosstab ON
tblResource.ResourceID =
qryCheck_ActualsVsForecasts_Crosstab.ResourceID) INNER JOIN
qryCheck_Resources ON qryCheck_ActualsVsForecasts_Crosstab.ResourceID
= qryCheck_Resources.ResourceID
GROUP BY qryCheck_Resources.Submitter, tblResource.Name,
qryCheck_ActualsVsForecasts_Crosstab.Category,
qryCheck_ActualsVsForecasts_Crosstab.Activity,
MonthName(Month([Month]),False), Year([Month]), nz([Actuals],0),
nz([Forecast],0)
ORDER BY qryCheck_Resources.Submitter, tblResource.Name,
qryCheck_ActualsVsForecasts_Crosstab.Activity;

I think I would prefer to create them as pdf, but I can create them
as .snp, then convert later if that gets too confusing.... hopefully
I didn't leave any other important info out...

 

[Duane Hookom]

I did provide a basic framework for looping through the table and creating
the reports. I expect you need to change this section of code to filter your
report and export/send it to your desired format.
strWhere = "IndividualID = " & !IndividualID
DoCmd.OpenReport "rptMonthlyInd" , acNormal, , strWhere
.MoveNext
You haven't suggested how you would filter the crosstab or record source .

You might want to google "Access Report to pdf" to find additional
information.