Question Batch Printing (1 Viewer)

[0bsidian]

Hi folks,

Hope you can help with this.

I currently have a database that is used to log customer transactions. The database also contains reports which are used as invoices for the various customers who have purchased items.

However at present, I can create one report which shows all transactions in the dbase and one which, with the use of a line in a query, allows the user to input the ID code for a customer to produce an individual invoice for that customer.

Given that there are 500+ customers at one time, I need a way of getting the database to produce all 500+ invoices for individial customers at (more or less) a click of a button. i.e. a batch printing process based on individual invoices

Can anyone offer any advice on how I would do this, as I am at a loss.

Many thanks in advance,

0bs

 

[MStef]

I think you need something like
"DemoCustomerReportA2000.mdb" (attachment, zip).
Open Form and try.

 

[0bsidian]

MStef, thanks for the prompt response (unlike this reply!).

Unfortunately, I don't think thats exactly what I am looking for - though it is closer.

I need each customer to have their own independent invoice (report), but all customers should have their invoice created (see: printed) at the same time.

So in essence, I need to figure out how to take the individual report I have already (generated by manually entering the ID you are looking for) and somehow create a script (or some such) which generates one of them for each of the IDs contained within the transaction table.

Additionally (and I should have noted this before), each invoice has a series of equations at the bottom, which sum up the various columns of data on the report, and I would need to ensure there are totals for each unique customer.

I hope that makes more sense than my previous post.

Thanks again,

0bs

 

[0bsidian]

Just a wee bump guys, in the hope that someone out there can help me remedy my problem. Unfortunately, I'm beginning to run short on the time I have to solve this problem.

If anyone can offer advice, I'd be most grateful.

0bs

 

[Mike375]

A simple but crude way to do it is

1) Make a Form based on a query that selects the relevant customers

2) Have the Report based on a query that uses the ID number on the form. Then if the Report is run from the Form it will be for the person whose record is displayed on the form.

3) With a simple macro or VBA you have it print the Report, then move to the next record in the Form and print the Report and so on through the records.

I use something similar except it is for Word docs that get information from the Form into bookmarks. You can set up the macro or VBA to run on a Timer event to match the printer, that is, it is a bit slower than the printer.

I am not sure how it would go for a batch of 500 because of memory as I am usually only doing small batches of 1 to 50 or so at a time.

 

[ajetrumpet]

if you uploaded the database, i bet you would get a solution.

 

[Rabbie]

I have a database that generates all the monthly invoices when you press a button. You can then print an individual invoice or all the the invoices for a given time period as required.

The method used is similar to Mike's in #5.

 

[0bsidian]

Thanks for the advice Mike.

I am however, a little confused (apologies, I know the basic design aspect of Access but dont have a great deal of experience beyond that, but can usually muddle my way through).

1. Would I be correct in assuming that the query can just be based on all transactions left to be invoiced (in my case, based soley on the entire contents of tblPupilTransaction?

2. I'm not sure I understand how to connect these elements together. If I have a form displaying the results of a query, record by record, how do I base the report on the ID in the form? (EDIT: Can I simply put a command button on the form and use "DoCmd.OpenReport" with some appropriate syntax to link to the ID field?)

3. Would this result in all records for that ID being printed, or simply that record? If the former, then can I expect duplicates when the same ID comes up in the recordset again? If the latter, this would create multiple invoices for the one customer.

Apologies if I am apparently looking to be spoon-fed here. I am very unsure what I am doing at this juncture and as a result of lack of time, I find myself risking being cheekily persistent.

Would it be useful if I attached an example db?

Thanks,

0bs

 

[Mike375]

1. Would I be correct in assuming that the query can just be based on all transactions left to be invoiced (in my case, based soley on the entire contents of tblPupilTransaction?

That is up to you. The query will have criteria that lists all the records/customers who you want to send an invoice.

2. I'm not sure I understand how to connect these elements together. If I have a form displaying the results of a query, record by record, how do I base the report on the ID in the form? (EDIT: Can I simply put a command button on the form and use "DoCmd.OpenReport" with some appropriate syntax to link to the ID field?)

In the query that your Report will be based you have for the criteria

[forms]![TheFormName]![TheForumID Name]

I would suggest you try it as a single record to print.

1) Make the Form/Query

2) Make the query that supports the Report have criteria as I outlined above.

On your form add a button or label that has a macro or code that will print the Report. You will see it prints the Report for the customer that was displayed on the form. After it prints scroll along to another record and clcik the label or button you made to print the Report and it will be for that customer.

The next stage would be to add to the code or macro that prints the Report a Move to Next Record action. If you did that then clicking on the label/button that printed the Report for that customer would move the form to the next record ready to be clicked again. When it is fully automated it is basically doing this except it is running the printing of the Report and moving to next record and repeating without you doing anything.

But I suspect you are best to get the basics of the queries, form and Report done first and whereby you can click on a label or button and print a Report for the customer displayed on the form.

 

[0bsidian]

Hi Mike,

I think I am clearer on that now - thank you.

However, jumping the gun slightly, assuming I can get it so it prints the report based on the record displayed on the form, how do I move from that, to getting it to print a cumulative report for all the entries under the specific displayed ID (and then move on to the next, ignoring an ID that is already printed).

That is to say, how can I use this functionality to print one invoice for each customer based on the entries in the table under their ID, in an automated fashion? at present I have a query that allows the user the input an ID to create a report based soley on that ID - I just want to get the database to a stage where it prints that sort of invoice for all entries, rather than have the human input.

Would using a combo box or the likes to select all the IDs present in the table work, or have I completely wandered off the path of your advice?

It's worth noting I may be being monumentally stupid, so please accept my apologies if thats the case.

0bs

 

[Mike375]

Hi Mike,

I think I am clearer on that now - thank you.

However, jumping the gun slightly, assuming I can get it so it prints the report based on the record displayed on the form, how do I move from that, to getting it to print a cumulative report for all the entries under the specific displayed ID (and then move on to the next, ignoring an ID that is already printed).

If I am reading you correctly you have more than one Report per customer. It will still be the same except your code or macro will instruct to print Report A, Report B etc and each of the reports will be based on a query that uses the criteria I outlined above.

But the type of Report is not as issue. When you have criteria in the query like [forms]![FormName![TextboxName] amd you open the query it says " can you display all the records where the entry in the ID field is the same as the entry in the ID field on Formxyz".

But you are jumping ahead. You first need to make a form/query and Report/query and where your Report query (or queries) has the record's ID that is dispalyed on the form.

If you make the basics like I suggested then you will get a picture of what happens and how it happens.

 

[0bsidian]

Thanks again Mike.

I have begun working on what you've described. I guess my fear is that I haven't explained myself clearly, and thus have influence your response in the negative, leading me to work on something that actually might not create the desired result.

I have only one report (for this function), but it allows the user to input which ID they would like to see. Once entering this, they get a fully formatted report, for that customer, with all their transactions on it and a total for the term.

To avoid having a user type in 500+ different IDs to produce these individual reports (invoices), I'd like some way of printing them all automatically.

Is that different from what you were thinking I was looking for and does that change your advice?

Regardless of the above, many many thanks for your assistance so far - it's invaluable.

0bs

 

[Mike375]

Thanks again Mike.


I have only one report (for this function), but it allows the user to input which ID they would like to see. Once entering this, they get a fully formatted report, for that customer, with all their transactions on it and a total for the term.

What is being suggested has the same result for a single customer Report. For example, you could have an index of customers and code would then find the record on the form and print the Report for that person.

But the difference is you want more than one customer's Report. The form is now based on a query that has selected all the relevant records. What I have outlined simply moves through the records printing a Report for each customer as it moves to that persons record. In essence it is a manual system that has been automated. In other words Access is clicking the "Print Report button" for you, moving to the next record and clicking the"Print Report button" for you and so on until it has moved through all the selected records.

This type of system is easy to make and very flexible, especially if you have some code or a macro that changes the record source of the form or even a simple filter. In other words you might want to send a Report to 10 customers based on some criteria. In that case the record source of the form would be changed so it now displayed only 10 records and then the print Report system (or Word docs with Bookmarks) would run. Or a customer phones you and says he lost the Report you sent last week. Then you go to his record and print the Report for him.

 

[0bsidian]

Mike,

Thanks for all your help here. I completed the various steps you had described and the process is almost done!

Admittedly, I am still trying to get the VBA code right to print, move to next record and stop at the final record, but I am sure I'll get that with a bit more effort.

Many thanks for your assistance!

0bs

 

[Mike375]

Will give you something to do between now and Christmas

 

[0bsidian]

Hi again Mike,

If only! I have to finish this up by tomorrow and am still having issues with this code (though by all accounts it should be relatively simple).

I've even tried some other methods and hit similar road bumps.

VBA is certainly not my strong suit, and I have no colleagues who know enough to assist. Having done this before, could I ask for your assistance once more so I can get this process working correctly?

I admit to being throughly out of my depth with VBA.

Many thanks regardless,

0bs

 

[Mike375]

There are different ways to do this and some might be better than others.

A form's OnCurrent event can be used to trigger macros or VBA. That will happen when the form moves to the next record.

You might also look at the RunMacro action. This where you have two macros and one macro runs the second macro. This can be done in VBA although I prefer a RunMacro action.

One that I use is a combination of the OnCurrent event and Timer event. The OnCurrent triggers print process and the Timer event moves to next record. My use of the Timer event is probably a hang over from days when printers were slower and more likely to jam. I set the timer to be a little slower than the printer.

You could also use the OnCurrent event and the last action line is MovetoNextRecord and then the OnCurrent event triggers everything again when it moves to the next record. This is the simplest way but sometimes with Access there can be problems when something is run from the one block of code or a macro.