Print individual reports for all outstanding invoices

[DMainland]

I use a report that generates all invoices outstanding for an individual
customer. I select the customer I want to report on from a dropdown list. I
would like to be able to print the same report for all customers who have
invoices outstanding, not just individual customers selected one at a time.
Any help would be greatly appreciated.

 

[Klatuu]

This involves using a couple of techniques together. First, the record
source for the report. I assume you are probably using a query for the
record source that filters on the value in the combo box. If this is the
case, remove the filtering at the query level, we will do it at the form
level.

Now, we need to use the "Adding All to a Combo Box" technique. Since I
don't know the structure of your client table or your combo box, I can't give
an exact answer, but for example purposes, I will assume your combo has two
columns, one for the ClientID which would be an autonumber primary key and
one for ClientName that the user actually sees and your combo's row source
query is something like:
SELECT ClientID, ClientName FROM tblClient ORDER BY ClientName;

So, change it to:
SELECT 0 AS ClientID, "(All)" AS ClientName FROM tblClient UNION SELECT
ClientID, ClientName FROM tblClient ORDER BY ClientName;

Now the user will see (All) as an option in the combo list. The next thing
to do is use the OpenReport method to open the report for a selected client
or all clients depending on whether the user selects (All). That you do
where you open the report. I assume it is in the Click event of a command
button. This would be an example for that:

Dim strWhere As String

If Me.cboSelectClient = 0 Then
strWhere = vbNullString
Else
strWhere = "[ClientID = " & Me.cboSelectClient
End IF

Docmd.OpenReport "ReportName", , , strWhere

 

[DMainland]

Thanks for your reply and answer. I will give it a try. I am no genius with
Access but I will follow your instructions and let you know how it turned
out. Thanks again.
--
DMainland

This involves using a couple of techniques together. First, the record
source for the report. I assume you are probably using a query for the
record source that filters on the value in the combo box. If this is the
case, remove the filtering at the query level, we will do it at the form
level.

Now, we need to use the "Adding All to a Combo Box" technique. Since I
don't know the structure of your client table or your combo box, I can't give
an exact answer, but for example purposes, I will assume your combo has two
columns, one for the ClientID which would be an autonumber primary key and
one for ClientName that the user actually sees and your combo's row source
query is something like:
SELECT ClientID, ClientName FROM tblClient ORDER BY ClientName;

So, change it to:
SELECT 0 AS ClientID, "(All)" AS ClientName FROM tblClient UNION SELECT
ClientID, ClientName FROM tblClient ORDER BY ClientName;

Now the user will see (All) as an option in the combo list. The next thing
to do is use the OpenReport method to open the report for a selected client
or all clients depending on whether the user selects (All). That you do
where you open the report. I assume it is in the Click event of a command
button. This would be an example for that:

Dim strWhere As String

If Me.cboSelectClient = 0 Then
strWhere = vbNullString
Else
strWhere = "[ClientID = " & Me.cboSelectClient
End IF

Docmd.OpenReport "ReportName", , , strWhere


--
Dave Hargis, Microsoft Access MVP

I use a report that generates all invoices outstanding for an individual
customer. I select the customer I want to report on from a dropdown list. I
would like to be able to print the same report for all customers who have
invoices outstanding, not just individual customers selected one at a time.
Any help would be greatly appreciated.