Is there a way to pass 2nd parameter to a report at run-time?

[Guest]

Hello, there.

I need your expert advice on how to pass a second parameter to a report at
run-time through code.

I have this custom developed system that processes reports on the backend
server.
For instance, rptInvoice takes in a where_Clause (InvNo="12345") with its
filter property at run-time. Now I need to pass it the recipient's fax number
as 2nd parameter.

The current system uses Caption to recieve the paramter, and by
doCmd.printOut, whth fax server spesified as printer, it just know to fax it
out.

Due to the change of the fax server, now the fax number is required to be on
the body of the report/document.

I have added a textbox on the page header, and I want to make its control
source = me!caption or me!Tag or something at design time. At run-time, by
assgining a value to that property, the data will be dynamically insert into
the field.

Anyway, I am not sure I am doing the right way because it is not working!

Thanks!

 

[Jeff L]

Unless I'm not understanding this correctly, it seems like you are
wanting to over complicate this process. Why do you need to pass the
fax number as a parameter? An invoice number would be tied to a client
and based on the client you would have the information you need,
including their fax number. Why don't you make the fax number a part
of your data source for the report? Then if you need to display it,
you can and don't have to pass it from somewhere. Am I missing
something?

 

[Larry Linson]

There are several ways to pass information to a Report that you open from
code.

One is to use the OpenArgs argument of DoCmd.OpenReport if you are using
Access 2002 or 2003. That argument can contain a number of parameters,
separated by some character that won't appear inside any of the parameters,
then using the Split function to separate them.

It may be somewhat more efficient, depending on your environment, to pass
the Where clause information and set it in the Record Source, but it is also
possible to pass it in the WhereCondition argument of the DoCmd.OpenReport.

Two other possibilities are: put the information in a Text Box on a Form
that will be Open and retrieve it using VBA code in the Report.

Another, but less-recommended, way is to pass the information in Public
Variables in a Standard Module. The reason this is less-recommended is that
such Variables can lose their value in case of an unhandled error in your
database.

Larry Linson
Microsoft Access MVP

 

[Guest]

Thank you Jeff!

Over 50% of the time, that is the case. Default customer fax number is the
one on the header and is the one the document will fax to. Even under this
scenario, due to the fact that the fax field has been called and located
differently from reports to reports ( i.e. it is on line 1, call Fax: in
invoice, and it is on line 3, called CUST Fx: on Credit Memo, and no such
field On ProductInfo). So the fax server is relying on a hidden field in the
header to know where to send.

The other scenario is that the user overrided the fax number when the sent
the request. When Users send the requests from the front end, they have an
option to type in a fax number. The reqeusts went into a queue table on the
server, and when server processes the request of print/fax/email the report,
now is where my challenge starts...

 

[Guest]

Thank a lot Larry!

I am going to try OpenArgs first. On the code, I have this:
DoCmd.OpenReport strReportName, acViewPreview, , strOpenArgs

However, I have difficulty to set up a placeholder on the report in design
mode to receive the value.
On the Page header, I have text1, I tried to set its control source to
=me!OpenArgs,
but Access wouldn't recognize it.

Also, I have used OpenArgs on OpenForm before. To use it under OpenReport,
what is the difference between OpenArgs and WhereCondition?