Page Break Macro/Code

[thepetey]

Hey,

I currently have a report in Access that is about 2000 pages which contains information on different stores and subtotals for each of the stores. Each store ranges from about 30-50 pages and is split by department then subtotaled at the end.

Currently we print the report and send it out to the stores which means 2000+ pages each week.

I was looking to print the pages double sided to reduce the paper usage, but when the report is an odd number, it causes two different stores to split on the same page. The pages have to be split by store since they go out to the different stores.

I figure a macro could be created to determine when the store group is an odd amount of pages and thus a page break can be added causing it to leave a blank sheet on the back side of the paper.

If anyone can help, please let me know!

Thank you ver much,
Peter

 

[Trevor G]

Peter welcome to the forum,

What about setting a macro to run reports for each store, you should get the same result, but they become individual reports. One macro should be able to do the job, and you can set the criteria in the macro window.

Select MACRO
In the actions select open report
In the arguments below then select the report
and set the where conditions to the store name from the table

repeat the steps

Trevor

 

[thepetey]

Hey Trevor,

Thanks for such a quick reply.
I attempted to do what you said, this allows me to open up individual reports.. therefore I am making a macro for each individual store.

Is there a way I can create a macro that runs and prints each of the macros that I made for the different stores (there are about 200+ stores) or do I have to run each macro individually and print it.

Thanks again!

Peter

 

[Trevor G]

Peter,

You could have had just one macro do it all, however what you can do in a new macro look for action called runMACRO then use the arguments below to find the macro, on the next action line do another runMACRO for the next one and so on. You can in the run report macro tell it to print rather than preview. so they will print one at a time.

Trevor

 

[thepetey]

Hey Trevor,

I made a single macro to hold all the stores and to make it Print when the macro is run. The only tiny problem now is that it prints on individual sheets, seems to ignore my current printer settings. I am looking to make it print double sided ofcourse.

Any fix for that?

Other then that, you are the best!

Thank you.

 

[Trevor G]

Peter I have read that you must use print or print preview, but have found a link with a sample database which deals with (I hope) this problem.

Access should always recognise your default printer and its settings, but there we go.

http://allenbrowne.com/ReportDuplex.html

Good luck

Trevor

 

[thepetey]

You are a God.

I feel like I keep asking you questions, testing out your Access/Macro Skills.

So here is my last questions, I promise!

Currently I have

OpenReport
Report Name: Customer Statement
View: Print
Where Cond: [Weekly Subsidy]![Store Num]=403

and so on and so forth for each store.

Is there a way i can make a [Weekly Subsidy]![Store Num]= Store Num

so that if i add or remove any stores in the future.. it just goes through each store and prints them?

Thank you!
I owe you alot

 

[Trevor G]

Peter,

I am sure there is a way for some code to do this,

I will look at this but will be next week before I can reply. For now if this happens it should be just a simple case of amending the MACRO.

Have a good weekend.

Trevor