Multiple Form Buttons Linking to the Same Report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

thedanmp - 30 Mar 2007 14:28 GMT

I have an invoice page on a form called “Projects.” I wanted to be able to
enter the contents of an invoice on a form and have a button create an
invoice in reports called “Commercial Invoice.” This part I have
successfully created.

However, I have some customers in Latin America that need a similarly styled
invoice in Spanish titled “Factura Comercial.” The information entered in
the form would be the same, but the invoice title should be different.

What I want is 2 buttons on the invoice form that link to the same report,
but based on the respective button, the “title” text field in the invoice
report (“InvTitle”) shows “Factura Comercial” when you click the button
“Factura Comercial” on the form and “Commercial Invoice” when you click the
“Commercial Invoice” button. Is there an if function you can put in a button
and/or report text field to change the field based on the button selection?

My thought process is that this would save space in the database by not
having to copy the invoice report into multiple reports for each button.

Reply to this Message

Rick Brandt - 30 Mar 2007 14:35 GMT

> I have an invoice page on a form called "Projects." I wanted to be
> able to enter the contents of an invoice on a form and have a button
[quoted text clipped - 17 lines]
> not having to copy the invoice report into multiple reports for each
> button.

If you are using a version of Access new enough to support OpenArgs for
Reports then you can pass two different values in the OpenArgs argument of
the OpenReport method and have code in the Open event of the report examine
that value and based on that decide what to display.

If your version does not support OpenArgs for reports (forms have had this
going way back) you can set a value in a hidden control on your form and
have the report reference that or use a global variable.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

thedanmp - 30 Mar 2007 16:08 GMT

I have Access 2003, but in the heading of the file it says “Access 2000 File
Format.” Don't know if my version or file is up to date enough for openargs.

I understand the concept of openargs, but do not have experience in using
them. I understand that I need to insert code in the event procedure in the
“on click” section of the button and I need to insert code in the “on open”
section of the report to communicate with the form, but I’m a relative
beginner at Access and wouldn’t know what to do.

Is there a simple code explanation, or would it be adviseable to just copy
the reports and make a separate invoice for each need? Here are the names I
have…

Form: Projects
(current) Button 1: Commercial Invoice
(future) Button 2: Factura Comercial

Current onclick event procedure for button 1…

Dim stDocName As String
Dim strWhere As String

stDocName = "Invoice"
strWhere = "[ProjectID]=" & Me!ProjectID
DoCmd.OpenReport stDocName, acPreview, , strWhere

Report: Invoice
Invoice Title Field: InvTitle

> > I have an invoice page on a form called "Projects." I wanted to be
> > able to enter the contents of an invoice on a form and have a button
[quoted text clipped - 26 lines]
> going way back) you can set a value in a hidden control on your form and
> have the report reference that or use a global variable.

Reply to this Message

fredg - 30 Mar 2007 17:21 GMT

> I have Access 2003, but in the heading of the file it says “Access 2000 File
> Format.” Don't know if my version or file is up to date enough for openargs.
[quoted text clipped - 55 lines]
>> going way back) you can set a value in a hidden control on your form and
>> have the report reference that or use a global variable.

You can use OpenArgs in Access 2003, but you don't really need to.

Simplest way is to add an OptionGroup to your form (instead of using
the 2 command buttons).
Add 2 radio buttons to the group.
Option1 = "Commercial Invoice"
Option2 = "Factura Comercia"

Then just one command button to open the report.

DoCmd.OpenReport "Invoice", acViewPreview, ,"[ProjectID]=" &
Me!ProjectID

Have an unbound control in the report header.
Set it's control source to:
=Choose(forms!FormName!OptionGroupName,"Commercial Invoice","Factura
Commercia")

The form must be open when the report is run. Depending upon the
selected option, either the English or Spanish text will display.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

thedanmp - 30 Mar 2007 22:20 GMT

That worked, thanks a lot!!!

One more thing really fast while I'm on the topic. I would also like to
have the invoice header fields like "description" and "unit price" and
"quantity" be in English or Spanish based on the choice in the option group
on the form. I attempted to constuct an iif function in the control source
of the text field in the report. Something like this:

=IIf([Invoicing]=1,"Quantity",IIf([Invoicing]=2,"Cantidad")

(invoicing is the name of my option group and 1 being the option number for
"Commercial Invoice" and 2 being the option number for "Factura Comercial")

This function is not working. Can you see anything wrong with my function
that I could try?

> > I have Access 2003, but in the heading of the file it says “Access 2000 File
> > Format.” Don't know if my version or file is up to date enough for openargs.
[quoted text clipped - 76 lines]
> The form must be open when the report is run. Depending upon the
> selected option, either the English or Spanish text will display.

Reply to this Message

fredg - 31 Mar 2007 01:17 GMT

> That worked, thanks a lot!!!
>
[quoted text clipped - 92 lines]
>> The form must be open when the report is run. Depending upon the
>> selected option, either the English or Spanish text will display.

Improperly written IIf statement.
1) Always make sure that the number of Open parenthesis minus the
number of close parenthesis = 0.
You have 2 open but just 1 close.

2) You have just the 2 choices, so if the value isn't 1, then it must
be 2.
=IIf([Invoicing]=1,"Quantity","Cantidad")
However, the Option Group is on the form, not on the report (isn't
that correct?), so you need to use the following syntax:

=IIf(forms!YourFormName![Invoicing]=1,"Quantity","Cantidad")

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message