Using Macro to print a different report for a list of different employees

[Jessica240]

Hello everyone! This should be a fairly easy question! I'm simply trying to create a macro that will open up a report and print it for each one of my employees each pay cycle. If you can see the attachment, there is a parameters box that I found in someone else's database, so I would like to do the same thing in mine but I can't figure out how to get the parameter part in my macro?





Basically, I was planning on creating a macro that would open a report and each macro would set the parameter to each different employee's name. Then it would print it and do the same thing for each of my employees. The parameter from the macro would feed into the query, generating a report unique for that employee.

Any help would be greatly appreciated.

Thanks!

Jessica

[ranman256]

I would not use it in the report, but instead the query.
On a report form , use a list (or combo) of employees.
On the PRINT button, cycle thru the list of empoyees, printing as you go.
Then when the employee is selected , the query will use it as its parameter.
The report dont care, it will run from the query.

The query will use the combo box (having the emp) as criteria...forms!frmRpts!cboEmp

'cycle thru all emps to print

Dim i As Integer
For i = 0 To cboEmp.ListCount - 1
   cboEmp= cboEmp.ItemData(i)
   DoCmd.OpenReport "rptMyRpt"
Next

[June7]

Macro not suitable for this, use VBA. One approach shown by ranman.

The alternative is one report set to force new page for each employee and open report filtered to desired employees. But will still probably need VBA code to construct filter criteria.

[Jessica240]

I have never used VBA before. I am a beginner with this type of stuff... I have been meaning to teach myself VBA but I just haven't had time. Thank you for the help though!

[June7]

Maybe misunderstood what you want. If you want to automate series of individual emails to send individual reports (batch output), that needs VBA. If you want to just input an employee id (and/or other parameters) and click button and send that one report, a macro could do that. If you have 100 employees that would be 100 input/clicks.

[Jessica240]

A coworker of mine designed a database that has an empty text box in a form. That text box is blank and seems pointless. However, it is actually a criteria for a query that is used to generate a report.

He has a macro with about 25 actions of opening a report and emailing it (like the "batch output" as you called it). Each macro has a different value in the parameter box which feeds into the text box in the form which then feeds into the query which then feeds into the report and then it gets printed.

So for example, the first macro action says open employee report with parameter = "John Smith"

So then that name feeds into the query which feeds into the report which then gets printed.

Then the next action query says open employee report with parameter = "Jane Doe".... and it does this for 25 employees.

I'm simply trying to recreate what he did, but am struggling because in my macro design I can't figure out where the options are to plug in a parameter value.

[June7]

Okay, I don't use macros and don't use dynamic parameter queries. So, yes, I guess a macro could somewhat do a 'batch' output. Sounds like the parameter (employee name or ID) is hard-coded into the macro actions (25 employees, 25 actions). If employees change, have to modify the macro.

Review http://www.fontstuff.com/access/acctut01.htm
Look at the section Asking the Questions in the Right Order
Maybe setting parameters like that in query has something to do with getting the Parameters to show in the macro.

[Jessica240]

Yes that is true - as employees change we would have to update the macros with the new names. I guess using VBA I could get around that?

VBA is intimidating BUT if I could teach myself how to use Access I suppose I could attempt to learn VBA :/

[June7]

Review:

https://support.office.com/en-US/Art...rs=en-US&ad=US

https://www.accessforums.net/access/...ing-47407.html

Need understanding of basic programming concepts, VBA vocabulary and syntax, and probably should get to know SQL (the language of queries). The Access query builder can help with constructing SQL statements that could be run within VBA. A good web source is http://www.w3schools.com/sql/default.asp

Example of looping through a recordset and outputting multiple individual reports https://www.accessforums.net/program...ort-41194.html

[pbaldy]

It sounds like you're on the wrong track. You don't want a macro for each department. I would have a form where the user selected the department, and have the query use that in its criteria.

[Jessica240]

I have a form that allows the user to select the criteria that matters for the report.

The reason I'm going with the macro route is because I'm trying to print out routine labor budget reports for each pay period for each department.

So the form lets the user select the department they want and then the user can choose from a variety of buttons on what they want to do with the information (such as view the report, export it, email it, etc.)

My goal is to have a macro for each department, then when the user clicks a button on the form, it runs that macro which prints off the report for each department.

Basically I'm just trying to get around using VBA. I don't have time to learn it by the time this project needs to get done. I have a database (lets call it DB-Supreme) that currently accomplishes the exact thing I'm trying to do - but I need to replicate it's methodology into my database.

So in DB-Supreme, when I create a macro to open a report, I select the report that it will open, and bam- the parameter box magically appears.

Now when I do the same thing in the database I'm working on, I create the macro, select the report it needs to open, and sadly no parameter box comes with it.

[Jessica240]

Basically I'm trying to do a batch output command - we have 26 departments and I want one macro to print the report for each department with one click of the button on the form.

[Jessica240]

I just solved the problem!!!!! I figured out that in the query, for the field that has the parameter in the criteria, I also need to add that parameter in the parameter box.... Wow I am so stoked!!!

[pbaldy]

Glad you got it sorted.