Printing report for mutiple customers

[Freddie81]

Evening guys,

I have a Database for invoicing purposes and I need to have it print customer statements for multiple customers.



I have a form called StatementsReportF that the user uses to select from a combo box if they want to print the statement for one customer or for all customers.

If "All Customers" is selected how would I go about getting Access to go through all customers that are active (has n Boolean value for IsActive) and print the report for every active customer without opening the report on screen for every one of them (let us say there's 25)?

[June7]

Apply filter criteria when opening report to return only Active customers or make this a static criteria in the report RecordSource query.

If you want each customer to be separate report, can build report that has grouping on customer ID. Set report to start new page after group footer.

[pbaldy]

I'd have the report return all of them, then use this method to restrict it if the user chose a single customer:

http://www.baldyweb.com/wherecondition.htm

[isladogs]

I would also print all required reports as a batch of 25(?).
One further tip I would suggest.
As well as grouping by customer, restart the page numbering for each customer.
So if each customer report is two pages, show Page 1 of 2 rather than Page 1 of 50.

[Freddie81]

The Query I'm using at the moment has the start date and end date as well as Customer selected in the criteria field. so if a user, for example, selects Tina Turner it will show results for only Tina Turner.

Is there a way to make "All customers" from the combo box tel the Query to run it for every customer ? Something like *.* in command prompt?

Or maybe:

use vba to check the combo box, if it is All Customers then it starts from the first one...prints the report...moves on to the second customer, prints the report and so on? Isn't there a For statement or something that can automatically increment the customer?

[June7]

You are using dynamic parameterized query? Post the query SQL statement. Maybe:

SELECT * FROM tableORquery WHERE … AND [customer] LIKE IIf(Forms!formname!comboboxname="All Customers", "*", Forms!formname!comboboxname)


I don't use dynamic parameterized query. I prefer VBA code build filter criteria and apply to report when it opens.

[Freddie81]

Okay here s the SQL for the Query

SELECT InvoiceT.InvoiceID AS [Doc Nr], " Invoice" AS Document, InvoiceT.InvoiceDate AS TransDate, InvoiceT.InvoiceTotal AS Amount, CompanyDetailsT.Company, CompanyDetailsT.Telephone, CompanyDetailsT.LogoPath, CustomersT.AccountNr, CustomersT.Telephone, CustomersT.CustomerID, CustomersT.Customer
FROM (CompanyDetailsT RIGHT JOIN CustomersT ON CompanyDetailsT.CompanyID = CustomersT.CompanyID) RIGHT JOIN InvoiceT ON CustomersT.CustomerID = InvoiceT.Customer
WHERE (((InvoiceT.InvoiceDate) Between [forms]![StatementsReportF]![txtStartDate] And [Forms]![StatementsReportF]![txtEndDate]))
ORDER BY InvoiceT.InvoiceID, InvoiceT.InvoiceDate
UNION ALL SELECT CreditNoteT.CreditNoteID AS [Doc Nr], " Credit Note" AS Document, CreditNoteT.CreditDate AS TransDate, [CreditNoteT].[TotalCredit]*-1 AS Amount, CompanyDetailsT.Company, CompanyDetailsT.Telephone, CompanyDetailsT.LogoPath, CustomersT.AccountNr, CustomersT.Telephone, CustomersT.CustomerID, CustomersT.Customer
FROM (CompanyDetailsT INNER JOIN CustomersT ON CompanyDetailsT.CompanyID = CustomersT.CompanyID) INNER JOIN CreditNoteT ON CustomersT.CustomerID = CreditNoteT.Customer
WHERE (((CreditNoteT.CreditDate) Between [forms]![StatementsReportF]![txtStartDate] And [Forms]![StatementsReportF]![txtEndDate]))
UNION ALL SELECT PaymentsT.InvoiceID AS [Doc Nr], " Payment" AS Document, PaymentsT.PaymentDate AS TransDate, [Amount]*-1 AS Expr1, CompanyDetailsT.Company, CompanyDetailsT.Telephone, CompanyDetailsT.LogoPath, CustomersT.AccountNr, CustomersT.Telephone, CustomersT.CustomerID, CustomersT.Customer
FROM (CompanyDetailsT INNER JOIN CustomersT ON CompanyDetailsT.CompanyID = CustomersT.CompanyID) INNER JOIN PaymentsT ON CustomersT.CustomerID = PaymentsT.Customer
WHERE (((PaymentsT.PaymentDate) Between [forms]![StatementsReportF]![txtStartDate] And [Forms]![StatementsReportF]![txtEndDate]));


Worst case scenario...I have to redo the query without the dynmic field for customersID. Then I'll change the button to use vba and include the where condition (will need some help wth that condition tho LOL)

[Micron]

If you're using a command button to execute the query, then code on that button's click event would allow you to
- ensure both date controls have a valid date (or just aren't empty)
- ensure a choice has been selected for the scope of the report
- build the sql statement based on the choice
- ensure the report is not already open (otherwise it will simply become the active window without automatically refreshing the report)
- open the report based on that sql

I think that's the approach I would take, unless someone has a better one.

Edit
You'd have to create 2 WHERE portions for the 2 criteria options. I presume you get "all" now because I see no "active" or any other such criteria in the posted sql.

[CJ_London]

just to clarify - you are printing and posting statements - or printing to a pdf and emailing?

[Freddie81]

Yes, the Query at the moment gets all the transactions in the date range selected for ALL customers. The start date and end date criteria are sorted in the query itself so I would just need the customerID one.

And yes, I am using a "Submit" command button to execute the report

[Freddie81]

just to clarify - you are printing and posting statements - or printing to a pdf and emailing?

I would like it to work for hard copy printing and pdf for email. I think pdf would take preference if I had to choose

[Micron]

Can you tweak code yourself if it's reasonably close (you haven't given enough info for an accurate shot at it)? To continue, at least the sql part for the active criteria is needed, or the table/fields that contain it.

If someone has a better idea, jump in 'cause for variables, now I'm seeing a need for 3 sql parts, 3 where parts, 1 orderby and 2 dates

[Freddie81]

OKay, let me try to explain a bit more....

On my form StatementsReportF There is a combo box with 2 options (1. All Customers, 2. select customer). If option 2 is chosen for select customer, a second combo box appears listing all the customers. The user then picks the start date and end date for the statement period and has to click submit to run the report.

Option 2 I managed to get working via vba on the submit button.

The problem now is I don't have any idea how to code the condition for option 1 ( All Customers)......the Query should run with CustomerID as primary criteria.

[pbaldy]

You can use a single combo with "All" as an option:

http://www.theaccessweb.com/forms/frm0043.htm

and this to use the null value of "All" to return all:

http://www.theaccessweb.com/queries/qry0001.htm

[Micron]

In addition to the design change posted, I'll add

suggest you develop 2 queries that work;
- one that uses date criteria AND returns all who are active
- one that uses date criteria AND returns all who are not
Simply enter the date criteria in query design for both, and the active criteria for both.

When the form is setup as you want, you'll run one query for one choice, the other for the other. Both queries would then be modified to accept your form field data instead of hard coded dates, etc. Would eliminate a lot of code. Drawback without code is the unpredictable results if you allow the query to run when any date is missing or invalid.