Automating printing Access Reports from parameter queries

[Vash!!!]

Hi all,

I have been trying to find a way to automate the printing of Access reports that are built from parameter queries. But I cant seem to find a way around this, so I was hoping someone could help me solve this issue.

Basically, I have to print thousands of Access Reports for customers each month. Each time I send a report to a printer, I have to specify the customer ID into the parameter field built into my report. For example, I have to enter something like, 'CustID555*' into the parameter field.

The report that is linked to my parameter query has this in the criteria field; 'Like [Enter CustID]'

It takes me a few days to print out all these reports.

Hence, is there a way to automate this using VBA or other methods?

Any ideas are greatly welcome!

Thanks!

 

[Kreszch68]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
there are several ways to do this. But, unless you want to print all customers at once, you'll need to think of how to select the range of customers you want to print the report for.<o></o>
If you want to print all customers, you could drop the where statement in your query, but probably this will result into one report with thousands of pages. So my guess is you want the reports generated one by one for each customer id.<o></o>
<o> </o>
Suppose you want to run reports for all customers from you customers table. <o></o>
<o> </o>
First you’ll have to create an array with all customer id’s. This could be a record set from your customers table. You’ll need something like this:<o></o>
<o> </o>

[COLOR=black][FONT=Verdana]Public Function SQL_CustomerID() As String<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    SQL_CustomerID = "Select CustomerID From Customers"<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Function<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Public Function RsFromSQL(ByVal sSQL As String) As Recordset<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim db As DAO.Database<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim rs As DAO.Recordset<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set db = CurrentDb<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set RsFromSQL = db.OpenRecordset(sSQL, dbOpenDynaset, dbReadOnly)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Function<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Public Sub LoopCustomers()<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim sSQL As String<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim oRs As DAO.Recordset<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]sSQL = SQL_CustomerID<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set oRs = RsFromSQL(sSQL)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]With oRs<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        .MoveFirst<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            'Loop all customers<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            Do Until .EOF<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                'If you only fetch the customerID field, field(0) is the correct field<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                'If you fetch more fields, replace field(0) with:<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                'Or field(n) where N is the number of the field holding the customerID, counting from 0.<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                'Or field("NameOfTheField")<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                Debug.Print .Fields(0).Value 'Test if you get values<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                'Here comes the action for each customer ID<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                'Something to pass the customer ID to the query you use.<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                'But at this point I don't know what you're using exactly.<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            .MoveNext<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on">Loop</st1:place><o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End With<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub<o:p></o:p>[/FONT][/COLOR]

<o> </o>

But as you can see, I'll need more information to give you the last part. From here I can't see how and where the parameter is passed to your query.

 

[Vash!!!]

Hi Kreszch68, thank you so much for replying. I cant thank you enough for that. How can I repay you back? Can I make some form of payment to you?

Yes, I only need to print selected customer reports for selected customers. Hence the need for the parameter query that prompts for customer IDs.

Also, when printing the reports for customers, I used at least three different reports from different tables and queries. But all reports are based on a parameter query that prompts for customer ID. All the different reports are printed in sequential order according to Customer ID.

For example, for Customer ID 44, I will print a report called, 'Monthly Expenditure'. I will type in '44' when the parameter query prompts for it. I will then send this report to the printer.

Next, I will print a report called, 'Monthly ageing'. I will type in, '44' when the parameter box prompts me for the customer ID. I will proceed to send this to the printer again.

I will continue to print different reports, but always in sequential order according to Customer ID, so that mailing out these hardcopy reports to customers are neat and tidy.

But I need to print out thousands of reports which takes me a few days to complete.

Hope you can help me out on this.

Thank you!

 

[Norie]

Why not create an unbound form with a multiselect listbox which a list of all the customers?

Then you can loop through the list and print the reports for the selected customers.

If you have more than one report that could also be incorporated in the userform, perhaps using a combobox.

The combobox would list the various reports, you would select the report you want to print.

Then you would select the customers you want to print the selected report for from the listbox and then print.

If you need to print all the reports for multiple customers then an 'All' option can be added to the combobox.

When that's selected all the reports are printed for each selected customer.

That would be the basics for selecting customers and reports.

However actually printing the reports could be a little trickier.

If you want to open/print a report in code you can use DoCmd.OpenReport, which actually has a 'where' argument which you can use to filter.

The possible problem I can see with the way you have things up is the use of parameters.

If there were no parameters you could simply use the where argument I mentioned to filter the report by customer ID.

So you would have to either remove the parameters from the reports/queries or find some way you can specify the parameter in code.

I'd still recommend the listbox/combobox approach for selecting what you want to print.