Use a Variable to set a Variable

[Michel Walsh]

It can be used where you have more than one parameter to send to your VBA
code behind the form/report and you don't want to relay on global variable
for each of those parameter, and/or when the value to string MAY be a
problem like a double value being truncated to just few decimal when
formatted to a string, or may even be formatted as what your prorgam will
read back as two integers delimited by a coma, if the default regional
setting is using a coma, rather than a dot, as decimal delimiter, to
'encode' the double number into a string. As example, in some European
countries, you can get:

? 5 / 2

2,5


which MAY be read back as two integers rather than a single float.

Using the collection avoid those conversions.


Sure, there is the global variable oCol, but that one is unlikely to collide
with another one of the same name, having a totally different use.



Vanderghast, Access MVP

Hi Michel,

That is some very useful information that I look forward to using. Thank
you.


Can you tell me what are some of the possible uses of the collection data
in
the Report or Form?

Best Regards
Pat Wood
http://gainingaccess.com

Using Split could be fine, but that assumes you may have to encode your
data
to strings.

Another solution is to use a collection of collections. Have a first
collection, global to the whole application, let us call it oCol.

Next, define all the arguments you want to pass inside a collection:

Dim mCol As New Collection
mCol.Add 33, "zozo"
mCol.Add Now(), "when"
mCol.Add 1 / 3, "oneThird"

here, 3 arguments, zozo, when, and oneThird are added to mCol.

Add the collection to oCol:

If oCol Is Nothing Then Set oCol = New Collection

oCol.Add mCol, "Report1"

And use the name you supplied, in oCol, for your collection as OpenArgs:

DoCmd.OpenReport "Report1", OpenArgs:="Report1"

You can then, inside the report, use:

Dim xCol As Collection
Set xCol = oCol(Me.OpenArgs)

and read each variable you want, like

Dim var As Double
var = xCol("OneThird")

No encoding required, and you can even pass complex objects (which may not
be easy to convert to string, or back from string)

Vanderghast, Access MVP

Hi dch3,

[quoted text clipped - 50 lines]

.OpenArgs the
same way.

 

[dch3]

Another prime reason for encapsulating the .OpenReport is that over the
weekend I realized that running a report isn't just running a report. Is the
report just going to be previewed (save a tree)? Is it going to be printed
direct to printer? Is it going to be outputted to a snapshot or other file
type? Or is the data going to be exported to Excel in a generic format for
use by the user?

Encapsulation puts all of that logic and code into a single Sub for use
throughout the database - and yes, the reports can be run from a variety of
points in the the app.

Yes. I now have a sub that explicity opens the report as in

Sub openRptLoadListDetail(Optional intView as Integer , Optional
strWhereStatement, Optional strReportTitle as String, Optional
strLoadListInformation as String)

[Code which builds the string for the open args - it only passes in a value
if a value was passed to here]

DoCmd.OpenReport [parameters]

end

Hi dch3,

A few posts back you seemed to indicate that you were passing values to a Sub
which then used DoCmd to Open the Report.

Are you using one sub to modify the WhereCondition & OpenArgs strings
*before* it they used as a DoCmd.OpenReport Parameter and then another sub to
use OpenArgs after the Report's On Open Event fires?

Best Regards,
Pat

Hi dch3,

[quoted text clipped - 7 lines]
properties and values of your Reports, such as the Title and RecordSource,
when certain values are passed to the Report using OpenArgs.
Yes. An external sub extracts the information from the OpenArgs string and
then loads up a global array for use when the information is needed. If this
were ASP, it'd just be a matter of refering to the QueryString collection of
the Request object.

You may be using the Split Function to create an Array from the OpenArgs,
which is a very nice way to handle multiple values passed in an OpenArgs
String.
Yes. Actually, I'm Spliting twice - first to extracted the parameter pair
and a second time to extract the parameter name and parameter value. The
string is formated as in
"parameter1Name=parameter1value;parameter2Name=parameter2value;parameter3Name=parameter3value"

I think you have a good idea there and there are a lot of interesting
opportunities with that approach.

What is confusing us, I think, is dealing with both the DoCmd.OpenReport
parameters and the OpenArgs. I would like to hear some more about what you
are doing to gain a better understanding.
It all comes down to reusing the same report object to provide different
information. Lets say that you have a report that shows Nationwide sides by
region by branch by sales person. Running the report as is provides ALL of
that information, but the sales person just wants their stuff or a regional
VP wants his/her region only. Obviously, you'll pass in a specific WHERE
statement to filter the records. But what if you want to just show TOTALS as
opposed to the detail. By manipulating the sections being printed you can
narrow down the content - just show sub totals or totals - all without
creating a whole new report. Naturally, you'll want to change the report
title. I'm using OpenArgs to do just that. In the end only one Access report,
but on the user side mutliple reports.

Best Regards,
Pat
[quoted text clipped - 22 lines]
and back to the point, all of the forms/reports hand their own .OpenArgs the
same way.

--
Hunter 57
http://churchmanagementsoftware.googlepages.com/home


http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200809/1