Programmatically passing variable as 'where' by parameter for Xtab

[BB_MIT]

Howdy folks, I'm looking for a little feedback on the following issue.

We have a fairly complex report that contains several sub-reports. Some of
the sub-reports are based upon crosstab queries that provide some aggregate
calculations. At present the crosstab queries utilize a "where" limiting
clause based upon a form's textbox value. While this method works fine it is
undesirable as the specific form must be open in order to use the report. We
would like to revise how the crosstab data is limited by using a variable
passed as a parameter if possible.

We have revised the "Open" event code in the sub-reports to create a
QueryDef object to which we are trying to assign a value to qdf.parameter(0).
Here is some sample code:

==========================================================
Set dbsReport = CurrentDb

'// Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Rpt_Recap_Cont_Sub_Xtab_Qry")

'// Set parameters for query based on values from parent report
qdf.Parameters(0) = Reports![Recap]![Txt_Job_id]

'// Open Recordset object.
Set rstReport = qdf.OpenRecordset()
Me.RecordSource = rstReport.Name

'// Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
==========================================================

The crosstab query include a single parameter of the long integer type and
the SQL string includes a "where" that should limit returned data based upon
that value.

The problem is that when the report is used accessed the user is prompted
with an input box requesting parameter input for the crosstab query.
Obviously we want to eliminate this prompt and have the parameter value
passed to the crosstab queries programmatically.

Any help that could be provided would be greatly appreciated.

Cordially,
Bob

 

[Jeff Boyce]

I'm not sure if this is relevant...

I noticed that you mentioned basing a WHERE clause on "a form's textbox",
but the code you listed contained a reference to a report's textbox. Am I
missing something?

Jeff Boyce
<Access MVP>

 

[BB_MIT]

No, you aren't missing anything. What you say is true. The sample code does
refer to a report control rather than a form control. That snippet of code
was written during workaround development where we moved reference from a
form to the top-level parent report. This solution works and eliminates the
need to have a specific form open in order to use the report but we would
still like to be able to pass a variable as parameter to the a QueryDef
object based upon a crosstab query without receiving an input prompt.

I'm not sure if this is relevant...

I noticed that you mentioned basing a WHERE clause on "a form's textbox",
but the code you listed contained a reference to a report's textbox. Am I
missing something?

Jeff Boyce
<Access MVP>

Howdy folks, I'm looking for a little feedback on the following issue.

We have a fairly complex report that contains several sub-reports. Some of
the sub-reports are based upon crosstab queries that provide some aggregate
calculations. At present the crosstab queries utilize a "where" limiting
clause based upon a form's textbox value. While this method works fine it is
undesirable as the specific form must be open in order to use the report. We
would like to revise how the crosstab data is limited by using a variable
passed as a parameter if possible.

We have revised the "Open" event code in the sub-reports to create a
QueryDef object to which we are trying to assign a value to qdf.parameter(0).
Here is some sample code:

==========================================================
Set dbsReport = CurrentDb

'// Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Rpt_Recap_Cont_Sub_Xtab_Qry")

'// Set parameters for query based on values from parent report
qdf.Parameters(0) = Reports![Recap]![Txt_Job_id]

'// Open Recordset object.
Set rstReport = qdf.OpenRecordset()
Me.RecordSource = rstReport.Name

'// Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
==========================================================

The crosstab query include a single parameter of the long integer type and
the SQL string includes a "where" that should limit returned data based upon
that value.

The problem is that when the report is used accessed the user is prompted
with an input box requesting parameter input for the crosstab query.
Obviously we want to eliminate this prompt and have the parameter value
passed to the crosstab queries programmatically.

Any help that could be provided would be greatly appreciated.

Cordially,
Bob

 

[Jeff Boyce]

One of the oft-used methods for passing a parameter to a query (for a report
or ...) is to do so in a form. Is there a reason why a "report order" form
is not relevant to the user interface you have designed?

Jeff Boyce
<Access MVP>

No, you aren't missing anything. What you say is true. The sample code does
refer to a report control rather than a form control. That snippet of code
was written during workaround development where we moved reference from a
form to the top-level parent report. This solution works and eliminates the
need to have a specific form open in order to use the report but we would
still like to be able to pass a variable as parameter to the a QueryDef
object based upon a crosstab query without receiving an input prompt.

I'm not sure if this is relevant...

I noticed that you mentioned basing a WHERE clause on "a form's textbox",
but the code you listed contained a reference to a report's textbox. Am I
missing something?

Jeff Boyce
<Access MVP>

Howdy folks, I'm looking for a little feedback on the following issue.

We have a fairly complex report that contains several sub-reports. Some of
the sub-reports are based upon crosstab queries that provide some aggregate
calculations. At present the crosstab queries utilize a "where" limiting
clause based upon a form's textbox value. While this method works fine

it
is

undesirable as the specific form must be open in order to use the

report.
We

would like to revise how the crosstab data is limited by using a variable
passed as a parameter if possible.

We have revised the "Open" event code in the sub-reports to create a
QueryDef object to which we are trying to assign a value to qdf.parameter(0).
Here is some sample code:

==========================================================
Set dbsReport = CurrentDb

'// Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Rpt_Recap_Cont_Sub_Xtab_Qry")

'// Set parameters for query based on values from parent report
qdf.Parameters(0) = Reports![Recap]![Txt_Job_id]

'// Open Recordset object.
Set rstReport = qdf.OpenRecordset()
Me.RecordSource = rstReport.Name

'// Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
==========================================================

The crosstab query include a single parameter of the long integer type and
the SQL string includes a "where" that should limit returned data

based
upon

that value.

The problem is that when the report is used accessed the user is prompted
with an input box requesting parameter input for the crosstab query.
Obviously we want to eliminate this prompt and have the parameter value
passed to the crosstab queries programmatically.

Any help that could be provided would be greatly appreciated.

Cordially,
Bob

 

[BB_MIT]

Mr. Boyce,

In a word, "portability". In two words, "self containment".

I'll try to be brief but clear although I'm not very good at it.

Requiring a form to be open in order to allow utilization of a program
resource is obviously a restraint that can limit the availability and
accessibility of the object in question. For maximum flexibility we try to
make reports as self sufficient or self reliant as possible. Case in point,
there is a program feature that allows end users to assemble "report
packets", a grouping of separate and individual reports that can be output as
a group rather than printing each individually. A prerequisite for report
inclusion with this feature is that the report must be independent of any
other UI components or end user interaction.

Ok, so not so brief and maybe not so clear.

Basically we have a feature that automates the printing of a bunch of
reports contained in a group. The contents of the report group is
customizable by the end user. In order to include this particular report we
must be able to eliminate any form dependency and any user input prompt.
Otherwise, the automated print process will fail because the required form is
not open or will stop at a input prompt.

Therefore, the best solution is to programmatically pass any information
that the report requires.

Hopefully I've explained myself well enough to be understood.

Thanks & Regards,
Bob

One of the oft-used methods for passing a parameter to a query (for a report
or ...) is to do so in a form. Is there a reason why a "report order" form
is not relevant to the user interface you have designed?

Jeff Boyce
<Access MVP>

No, you aren't missing anything. What you say is true. The sample code does
refer to a report control rather than a form control. That snippet of code
was written during workaround development where we moved reference from a
form to the top-level parent report. This solution works and eliminates the
need to have a specific form open in order to use the report but we would
still like to be able to pass a variable as parameter to the a QueryDef
object based upon a crosstab query without receiving an input prompt.

I'm not sure if this is relevant...

I noticed that you mentioned basing a WHERE clause on "a form's textbox",
but the code you listed contained a reference to a report's textbox. Am I
missing something?

Jeff Boyce
<Access MVP>

Howdy folks, I'm looking for a little feedback on the following issue.

We have a fairly complex report that contains several sub-reports. Some of
the sub-reports are based upon crosstab queries that provide some
aggregate
calculations. At present the crosstab queries utilize a "where" limiting
clause based upon a form's textbox value. While this method works fine it
is
undesirable as the specific form must be open in order to use the report.
We
would like to revise how the crosstab data is limited by using a variable
passed as a parameter if possible.

We have revised the "Open" event code in the sub-reports to create a
QueryDef object to which we are trying to assign a value to
qdf.parameter(0).
Here is some sample code:

==========================================================
Set dbsReport = CurrentDb

'// Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Rpt_Recap_Cont_Sub_Xtab_Qry")

'// Set parameters for query based on values from parent report
qdf.Parameters(0) = Reports![Recap]![Txt_Job_id]

'// Open Recordset object.
Set rstReport = qdf.OpenRecordset()
Me.RecordSource = rstReport.Name

'// Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
==========================================================

The crosstab query include a single parameter of the long integer type and
the SQL string includes a "where" that should limit returned data based
upon
that value.

The problem is that when the report is used accessed the user is prompted
with an input box requesting parameter input for the crosstab query.
Obviously we want to eliminate this prompt and have the parameter value
passed to the crosstab queries programmatically.

Any help that could be provided would be greatly appreciated.

Cordially,
Bob

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Instead of a Form or a Report reference why not use a function. E.g.:

SELECT *
FROM table
WHERE column = getParameterValue()

The function would be like this:

Public Function getParameterValue() As Variant
getParameterValue = g_strQueryParameter
End Function

The module variable g_strQueryParameter would be in a public VBA
module's Declaration section.

Public g_strQueryParameter As String

Before running the report, set up the variable

g_strQueryParameter = 'haberdasher'
DoCmd.OpenReport ... etc. ...

When the query runs it will retrieve 'haberdasher' for the WHERE
evaluation.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQie004echKqOuFEgEQJipwCgwdKD/ET5uy7svNH45p29LZHqaj0An0vR
nh1vnisfaMGTXi799d1fvtMf
=F4Wl
-----END PGP SIGNATURE-----

Mr. Boyce,

In a word, "portability". In two words, "self containment".

I'll try to be brief but clear although I'm not very good at it.

Requiring a form to be open in order to allow utilization of a program
resource is obviously a restraint that can limit the availability and
accessibility of the object in question. For maximum flexibility we try to
make reports as self sufficient or self reliant as possible. Case in point,
there is a program feature that allows end users to assemble "report
packets", a grouping of separate and individual reports that can be output as
a group rather than printing each individually. A prerequisite for report
inclusion with this feature is that the report must be independent of any
other UI components or end user interaction.

Ok, so not so brief and maybe not so clear.

Basically we have a feature that automates the printing of a bunch of
reports contained in a group. The contents of the report group is
customizable by the end user. In order to include this particular report we
must be able to eliminate any form dependency and any user input prompt.
Otherwise, the automated print process will fail because the required form is
not open or will stop at a input prompt.

Therefore, the best solution is to programmatically pass any information
that the report requires.

Hopefully I've explained myself well enough to be understood.

Thanks & Regards,
Bob


:

One of the oft-used methods for passing a parameter to a query (for a report
or ...) is to do so in a form. Is there a reason why a "report order" form
is not relevant to the user interface you have designed?

Jeff Boyce
<Access MVP>

No, you aren't missing anything. What you say is true. The sample code
does

refer to a report control rather than a form control. That snippet of code
was written during workaround development where we moved reference from a
form to the top-level parent report. This solution works and eliminates
the

need to have a specific form open in order to use the report but we would
still like to be able to pass a variable as parameter to the a QueryDef
object based upon a crosstab query without receiving an input prompt.

:


I'm not sure if this is relevant...

I noticed that you mentioned basing a WHERE clause on "a form's
textbox",

but the code you listed contained a reference to a report's textbox. Am
I

missing something?

Jeff Boyce
<Access MVP>


Howdy folks, I'm looking for a little feedback on the following issue.

We have a fairly complex report that contains several sub-reports.

Some of

the sub-reports are based upon crosstab queries that provide some

aggregate

calculations. At present the crosstab queries utilize a "where"
limiting

clause based upon a form's textbox value. While this method works fine
it

is

undesirable as the specific form must be open in order to use the
report.

We

would like to revise how the crosstab data is limited by using a
variable

passed as a parameter if possible.

We have revised the "Open" event code in the sub-reports to create a
QueryDef object to which we are trying to assign a value to

qdf.parameter(0).

Here is some sample code:

==========================================================
Set dbsReport = CurrentDb

'// Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Rpt_Recap_Cont_Sub_Xtab_Qry")

'// Set parameters for query based on values from parent report
qdf.Parameters(0) = Reports![Recap]![Txt_Job_id]

'// Open Recordset object.
Set rstReport = qdf.OpenRecordset()
Me.RecordSource = rstReport.Name

'// Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
==========================================================

The crosstab query include a single parameter of the long integer type
and

the SQL string includes a "where" that should limit returned data
based

upon

that value.

The problem is that when the report is used accessed the user is
prompted

with an input box requesting parameter input for the crosstab query.
Obviously we want to eliminate this prompt and have the parameter
value

passed to the crosstab queries programmatically.

Any help that could be provided would be greatly appreciated.

Cordially,
Bob

 

[BB_MIT]

MGFoster,

Global var & get function: this is as good of a solution as any.

Thanks

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Instead of a Form or a Report reference why not use a function. E.g.:

SELECT *
FROM table
WHERE column = getParameterValue()

The function would be like this:

Public Function getParameterValue() As Variant
getParameterValue = g_strQueryParameter
End Function

The module variable g_strQueryParameter would be in a public VBA
module's Declaration section.

Public g_strQueryParameter As String

Before running the report, set up the variable

g_strQueryParameter = 'haberdasher'
DoCmd.OpenReport ... etc. ...

When the query runs it will retrieve 'haberdasher' for the WHERE
evaluation.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQie004echKqOuFEgEQJipwCgwdKD/ET5uy7svNH45p29LZHqaj0An0vR
nh1vnisfaMGTXi799d1fvtMf
=F4Wl
-----END PGP SIGNATURE-----

Mr. Boyce,

In a word, "portability". In two words, "self containment".

I'll try to be brief but clear although I'm not very good at it.

Requiring a form to be open in order to allow utilization of a program
resource is obviously a restraint that can limit the availability and
accessibility of the object in question. For maximum flexibility we try to
make reports as self sufficient or self reliant as possible. Case in point,
there is a program feature that allows end users to assemble "report
packets", a grouping of separate and individual reports that can be output as
a group rather than printing each individually. A prerequisite for report
inclusion with this feature is that the report must be independent of any
other UI components or end user interaction.

Ok, so not so brief and maybe not so clear.

Basically we have a feature that automates the printing of a bunch of
reports contained in a group. The contents of the report group is
customizable by the end user. In order to include this particular report we
must be able to eliminate any form dependency and any user input prompt.
Otherwise, the automated print process will fail because the required form is
not open or will stop at a input prompt.

Therefore, the best solution is to programmatically pass any information
that the report requires.

Hopefully I've explained myself well enough to be understood.

Thanks & Regards,
Bob


:

One of the oft-used methods for passing a parameter to a query (for a report
or ...) is to do so in a form. Is there a reason why a "report order" form
is not relevant to the user interface you have designed?

Jeff Boyce
<Access MVP>


No, you aren't missing anything. What you say is true. The sample code

does

refer to a report control rather than a form control. That snippet of code
was written during workaround development where we moved reference from a
form to the top-level parent report. This solution works and eliminates

the

need to have a specific form open in order to use the report but we would
still like to be able to pass a variable as parameter to the a QueryDef
object based upon a crosstab query without receiving an input prompt.

:


I'm not sure if this is relevant...

I noticed that you mentioned basing a WHERE clause on "a form's

textbox",

but the code you listed contained a reference to a report's textbox. Am

I

missing something?

Jeff Boyce
<Access MVP>


Howdy folks, I'm looking for a little feedback on the following issue.

We have a fairly complex report that contains several sub-reports.

Some of

the sub-reports are based upon crosstab queries that provide some

aggregate

calculations. At present the crosstab queries utilize a "where"

limiting

clause based upon a form's textbox value. While this method works fine

it

is

undesirable as the specific form must be open in order to use the

report.

We

would like to revise how the crosstab data is limited by using a

variable

passed as a parameter if possible.

We have revised the "Open" event code in the sub-reports to create a
QueryDef object to which we are trying to assign a value to

qdf.parameter(0).

Here is some sample code:

==========================================================
Set dbsReport = CurrentDb

'// Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Rpt_Recap_Cont_Sub_Xtab_Qry")

'// Set parameters for query based on values from parent report
qdf.Parameters(0) = Reports![Recap]![Txt_Job_id]

'// Open Recordset object.
Set rstReport = qdf.OpenRecordset()
Me.RecordSource = rstReport.Name

'// Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
==========================================================

The crosstab query include a single parameter of the long integer type

and

the SQL string includes a "where" that should limit returned data

based

upon

that value.

The problem is that when the report is used accessed the user is

prompted

with an input box requesting parameter input for the crosstab query.
Obviously we want to eliminate this prompt and have the parameter

value

passed to the crosstab queries programmatically.

Any help that could be provided would be greatly appreciated.

Cordially,
Bob