Loops, VBA, reports, exports

[paddgirl21]

Hello, All!

I'm hopeful that anyone could help me out with a slight problem of mine. I am trying to accomplish this:

• There are two controls on the form where a button is (form named ExportSelector) which the user indicates which department and what time frame they want.
• I have a button labeled MultipleExportbtn that when pressed it takes a query that I have built with two paramaters being used: Department and TimeFrame. (the controls that the user defined).
• Once the button is pressed, it opens up a report based on the query I made. The report will find those who are in a certain department with metrics within the time frame.

The problem happens where, I would like the report to come out on separate sheets of paper. The Force New Page does not work because it forces the page to happen not on the Employee ID which is where I need it to (so each person has their own page).

From there, it can automatically save the reports to a specified folder I have designated it. That, part I figured out--when I only have 1 person I am trying to do a report on it is easy--not so easy when there are multiple people that it has to loop through. It becomes messy when it is trying to loop through the entire department's individuals.

Each individual has a set of metrics it displays on the report. I need these to stay together.

There being 3 tables that are being used: Employee, Metrics, Department

It must be something simple... I just can't figure out what it is. I have never worked with Loops in VBA before... so if that is the answer, I'll be learning something new!

Anyone have any suggestions for me to start with?

 

[Cronk]

If I understand correctly, you have a report with grouping on the Employee.

If you want to start a new page for each employee, then add a footer for this group and insert a page break.

 

[TJPoorman]

There is also an option to keep the grouping on the same page:

 

[paddgirl21]

If I understand correctly, you have a report with grouping on the Employee.

If you want to start a new page for each employee, then add a footer for this group and insert a page break.

Hmm, tried this and nothing.

 

[paddgirl21]

I tried a combination of both of your suggestions which almost does the trick except that it puts an extra page in after each employee. I grouped on EID and kept the whole group together on one page while putting a page break in the footer EID section. If I put a page break in the header EID section, of course, it puts a page break in before the section. I could try to apply conditional break if a certain condition is met. :-/

This is very strange.

 

[paddgirl21]

Ahh, yes.. as long as I put in force new page on the EID footer section it worked great without a page break. :-/ Man... sometimes you just need to walk away and come back.

You gave me some good ideas and it worked. Thanks guys!