Choosing One of two Available Report Formats

[notoca]

Hi

The following is code for a command button in a form. When
clicked it produces a report based upon the current record
of the form.:

The following procedure prints only the record that you
select from the Customers form in the sample database
Northwind.mdb.



Open the Customers form in Design view. Add a command
button to the form, and then set the following command
button properties:
Name: cmdPrintRecord
Caption: Print Record
OnClick: [Event Procedure]

Set the OnClick property to the following event
procedure:
Private Sub cmdPrintRecord_Click()

Dim strReportName As String
Dim strCriteria As String

strReportName = "rptPrintRecord"
strCriteria = "[CustomerID]='" & Me![CustomerID] & "'"
DoCmd.OpenReport strReportName, acViewPreview, ,
strCriteria

End Sub

I would like to take this once step further. On my form I
have a unbound field that calculates the total cost of an
invoice. Now for the unusual bit. The total amount
calculated determines which one of two reports should be
printed. The criteria is if the amount is less than $51
then "report1" is used, otherwise if it is greater than
$50 then "report2" is used. I know this sounds wierd but
this is the system we have to work with.

So, is it possible to adjust this code to choose which
report to open based on the total amount of the invoice
record?

Thanks

notoca

 

[Marshall Barton]

The following is code for a command button in a form. When
clicked it produces a report based upon the current record
of the form.:

The following procedure prints only the record that you
select from the Customers form in the sample database
Northwind.mdb.


Open the Customers form in Design view. Add a command
button to the form, and then set the following command
button properties:
Name: cmdPrintRecord
Caption: Print Record
OnClick: [Event Procedure]

Set the OnClick property to the following event
procedure:
Private Sub cmdPrintRecord_Click()

Dim strReportName As String
Dim strCriteria As String

strReportName = "rptPrintRecord"
strCriteria = "[CustomerID]='" & Me![CustomerID] & "'"
DoCmd.OpenReport strReportName, acViewPreview, ,
strCriteria

End Sub

I would like to take this once step further. On my form I
have a unbound field that calculates the total cost of an
invoice. Now for the unusual bit. The total amount
calculated determines which one of two reports should be
printed. The criteria is if the amount is less than $51
then "report1" is used, otherwise if it is greater than
$50 then "report2" is used. I know this sounds wierd but
this is the system we have to work with.

So, is it possible to adjust this code to choose which
report to open based on the total amount of the invoice
record?

Change the code to select the desired report. Instead of
the line:

strReportName = "rptPrintRecord"

use something more like this:

If txtTotalAmt > 50 Then
strReportName = "report1"
Else
strReportName = "report2"
End If

 

[notoca]

Hi

It works, but now I need to include info in the report
from 2 other tables as well as the main one. I will need
to have a query to bring the info from all 3 tables. Can I
do the same Magic when clicking a button on the main form
and producing one of two reports via a query for the
current record being displayed in the main form. Does that
make sense.

notoca

-----Original Message-----

The following is code for a command button in a form. When
clicked it produces a report based upon the current record
of the form.:

The following procedure prints only the record that you
select from the Customers form in the sample database
Northwind.mdb.


Open the Customers form in Design view. Add a command
button to the form, and then set the following command
button properties:
Name: cmdPrintRecord
Caption: Print Record
OnClick: [Event Procedure]

Set the OnClick property to the following event
procedure:
Private Sub cmdPrintRecord_Click()

Dim strReportName As String
Dim strCriteria As String

strReportName = "rptPrintRecord"
strCriteria = "[CustomerID]='" & Me![CustomerID] & "'"
DoCmd.OpenReport strReportName, acViewPreview, ,
strCriteria

End Sub

I would like to take this once step further. On my form I
have a unbound field that calculates the total cost of an
invoice. Now for the unusual bit. The total amount
calculated determines which one of two reports should be
printed. The criteria is if the amount is less than $51
then "report1" is used, otherwise if it is greater than
$50 then "report2" is used. I know this sounds wierd but
this is the system we have to work with.

So, is it possible to adjust this code to choose which
report to open based on the total amount of the invoice
record?

Change the code to select the desired report. Instead of
the line:

strReportName = "rptPrintRecord"

use something more like this:

If txtTotalAmt > 50 Then
strReportName = "report1"
Else
strReportName = "report2"
End If

 

[Marshall Barton]

It works, but now I need to include info in the report
from 2 other tables as well as the main one. I will need
to have a query to bring the info from all 3 tables.

Use the query design screen to add the other two tables.
Then make sure the tables related fields have the
appropriate line between them.

Can I
do the same Magic when clicking a button on the main form
and producing one of two reports via a query for the
current record being displayed in the main form. Does that
make sense.

No, it doesn't make sense to me? Your code already filters
the report to the current customer ID
--
Marsh
MVP [MS Access]

-----Original Message-----

The following is code for a command button in a form. When
clicked it produces a report based upon the current record
of the form.:

The following procedure prints only the record that you
select from the Customers form in the sample database
Northwind.mdb.


Open the Customers form in Design view. Add a command
button to the form, and then set the following command
button properties:
Name: cmdPrintRecord
Caption: Print Record
OnClick: [Event Procedure]

Set the OnClick property to the following event
procedure:
Private Sub cmdPrintRecord_Click()

Dim strReportName As String
Dim strCriteria As String

strReportName = "rptPrintRecord"
strCriteria = "[CustomerID]='" & Me![CustomerID] & "'"
DoCmd.OpenReport strReportName, acViewPreview, ,
strCriteria

End Sub

I would like to take this once step further. On my form I
have a unbound field that calculates the total cost of an
invoice. Now for the unusual bit. The total amount
calculated determines which one of two reports should be
printed. The criteria is if the amount is less than $51
then "report1" is used, otherwise if it is greater than
$50 then "report2" is used. I know this sounds wierd but
this is the system we have to work with.

So, is it possible to adjust this code to choose which
report to open based on the total amount of the invoice
record?

Change the code to select the desired report. Instead of
the line:

strReportName = "rptPrintRecord"

use something more like this:

If txtTotalAmt > 50 Then
strReportName = "report1"
Else
strReportName = "report2"
End If