Openrecordset Query with Parameter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have report that summaizes rolling periods. It is based on a Crosstab
query, but some label captions and rowsource properties of fields change as
the column headings potentially change each time the report is run.

So I am using DAO statements on the Open event of the report to retrieve the
field names from the crosstab query and assign them to the control source
property of the appropriate fields with:

Me.Q8.ControlSource = CurrentDb.QueryDefs("Z1").Fields(5).Name

(Z1 is the name of the query)

I use similar statements on the format property of a groupheader as well.

This works great until I add a parameter to the crosstab query. I get the
run-time error '3265' Item not found in this collection error. Do I have to
use openrecordset and retrieve the field names? If so, Can I use:

Currentdb.OpenRecordset("Z1") somehow when a parameter is required, or do I
have to provide the SQL string itself, define the parameter value, put it in
the string, etc.? Then get the field names and run the report and allow the
report query to pull the parameters from an open form? If so, can anyone
send an example?

Or Is there an easier way?

Thanks in advance.
 
Let me first say that I may not completely understand all the specifics of
your situation.

If you are using a form to open the report, and the form contains a field
with the parameter value in question, then it would be possible to have the
"Z1" just reference the parameter value on the form, which I am assuming
will be open when the report is run. For example, the criteria section of
the Z1 query could be referenced as follows:

Forms!MyReportForm!txtMyParamaterValue

I have done something similar and it has worked.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have report that summaizes rolling periods. It is based on a Crosstab
query, but some label captions and rowsource properties of fields change as
the column headings potentially change each time the report is run.

So I am using DAO statements on the Open event of the report to retrieve the
field names from the crosstab query and assign them to the control source
property of the appropriate fields with:

Me.Q8.ControlSource = CurrentDb.QueryDefs("Z1").Fields(5).Name

(Z1 is the name of the query)

I use similar statements on the format property of a groupheader as well.

This works great until I add a parameter to the crosstab query. I get the
run-time error '3265' Item not found in this collection error. Do I have to
use openrecordset and retrieve the field names? If so, Can I use:

Currentdb.OpenRecordset("Z1") somehow when a parameter is required, or do I
have to provide the SQL string itself, define the parameter value, put it in
the string, etc.? Then get the field names and run the report and allow the
report query to pull the parameters from an open form? If so, can anyone
send an example?

Or Is there an easier way?

Thanks in advance.
 
Thanks for your response, and I know it is confusing.

I think what you suggested will work, the problem is that the report is
based on a crosstab query, so in effect, the fields can potentially change
each time the report is run (column headings changing).

So, the control source property of some of fields in the report cannot
remain static; they must change when the column headings change. In order to
change them at run-time, I use DAO to retrieve the name of the fields in the
query and assign it as the ControlSourceof the appropriate fields.

This was working fine until I added a parameter to the query (to be
ultimately a field from a form as you suggested). The error occurs because
Access (DAO) cannot retrieve field names from a parameterized query.

It is the DAO portion of the process that is crashing. I need a way to apply
the parameters to the query and retrive the field names. I am going to look
at an ADO solution next.
 
rs.Fields(index).name will always return the field names when using DAO
(and I think also ADO, but I'm always using the latter).

rs - object variable representing the recordSet being examined
Fields - the fields collection
index - ordinal index of the field being examined
 
Thanks David, but that does not work. It used to before I parameterized the
query. Witha parameter assigned to the query, even if it refers to a field
on an open form, like [Forms]![Options]![Department], it still wont grab the
field.

Now I am writing a separate procedure to retrieve the field names, i.e.
using DAO to run the query witht the parameter and get the field name. I am
stuck on the syntax of the last line of the SQL string though; it is
concatenating fields with static text:

Dim QName As DAO.Recordset
Dim SQL As String
Dim dpt As String
Dim cont As String

dpt = Forms!Options!Department

SQL = "TRANSFORM First(FINANCIALS.V) AS FirstOfV " & _
"SELECT FINANCIALS.CATORD, FINANCIALS.EXPORD, FINANCIALS.DEPT,
FINANCIALS.CAT, FINANCIALS.EXPTYPE " & _
"FROM CONTROL INNER JOIN FINANCIALS ON (CONTROL.Q = FINANCIALS.Q) AND
(CONTROL.Y = FINANCIALS.Y) " & _
"WHERE(((FINANCIALS.DEPT) = '" & dpt & "')) " & _
"GROUP BY FINANCIALS.CATORD, FINANCIALS.EXPORD, FINANCIALS.DEPT,
FINANCIALS.CAT, FINANCIALS.EXPTYPE " & _
"PIVOT [CONTROL].[Y]" & ", Qtr " & " [CONTROL].[Q];"<<<
Click to expand...
Click to expand...
Click to expand...

Set QName = CurrentDb.OpenRecordset(SQL)

Field = QName.Fields(5).Name

QName.Close
 
In the past, I have created a dynamic crosstab query report where I have set
the ControlSource property through code. I was able to use DAO to do this,
and the underlying queries did have parameter values. DAO can retrieve the
field values from parameterized queries, it just needs to be able to access
the value(s) of the parameter(s) in order to do so.

Crosstab queries are different from other Access queries in that, due to the
dynamic nature of the columns, the fields for the query cannot be stored
when the query is created. The only way for a crosstab query to generate
the field names then is for the query to actually execute itself. The
exception to this is when you use the PIVOT IN syntax in the crosstab and
specify the column names explicitly.

In the case of a parameterized crosstab query (without the PIVOT IN syntax),
obviously, it will need to be able to reference the parameter value(s) in
order do so. The parameter(s) may not actually affect which fields are
generated by the query, however, the values are still needed to execute the
query.

Since I am assuming you are not using the PIVOT IN syntax, one alternative
is to use the OpenRecordset method of the QueryDef and use the Fields
collection of the recordset to set the ControlSource property. For example:

Set db = CurrentDb
Set qdf = db.QueryDefs("Z1")

qdf.Parameters("[FORMS]![REPORTFORM]![txtMyParameterValue]").Value =
[FORMS]![REPORTFORM]![txtMyParameterValue]
Set rs = qdf.OpenRecordset
Me.Q8.ControlSource = rs.Fields(5).Name

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Thanks for your response, and I know it is confusing.

I think what you suggested will work, the problem is that the report is
based on a crosstab query, so in effect, the fields can potentially change
each time the report is run (column headings changing).

So, the control source property of some of fields in the report cannot
remain static; they must change when the column headings change. In order
to
change them at run-time, I use DAO to retrieve the name of the fields in the
query and assign it as the ControlSourceof the appropriate fields.

This was working fine until I added a parameter to the query (to be
ultimately a field from a form as you suggested). The error occurs because
Access (DAO) cannot retrieve field names from a parameterized query.

It is the DAO portion of the process that is crashing. I need a way to apply
the parameters to the query and retrive the field names. I am going to look
at an ADO solution next.
 
DEI said:
I have report that summaizes rolling periods. It is based on a
Crosstab query, but some label captions and rowsource properties of
fields change as the column headings potentially change each time the
report is run.

So I am using DAO statements on the Open event of the report to
retrieve the field names from the crosstab query and assign them to
the control source property of the appropriate fields with:

Me.Q8.ControlSource = CurrentDb.QueryDefs("Z1").Fields(5).Name

(Z1 is the name of the query)

I use similar statements on the format property of a groupheader as
well.

This works great until I add a parameter to the crosstab query. I
get the run-time error '3265' Item not found in this collection
error. Do I have to use openrecordset and retrieve the field names?
If so, Can I use:

Currentdb.OpenRecordset("Z1") somehow when a parameter is required,
or do I have to provide the SQL string itself, define the parameter
value, put it in the string, etc.? Then get the field names and run
the report and allow the report query to pull the parameters from an
open form? If so, can anyone send an example?

Or Is there an easier way?

Thanks in advance.
Click to expand...

I'm not completely following this, but you might be able to get Access
to resolve the parameters in the querydef, and then open the recordset
from the querydef:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qdf As db.QueryDefs("Z1")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next

Set rs = qdf.OpenRecordset

If all you want is the field names, you *may* be able to refer to the
querydef's Fields collection after having resolved the parameters,
without having to open a recordset. I'm not sure about that, as I
haven't tried it.
 
Thanks guys for your help. I will take a look at the options you described.

What I actually ended up doing:

I did use the PIVOT IN clause in the query statement, and grabbed the field
titles, which are dynamic, from another source. The PIVOT IN clause is
redefined each time the report is run. Similarly, I change the contolsource
properties of the field headings in the report to line up. I will look at
your solutions, however, because I think I might be able to make it more
efficient.

The next problem I had was adding a sub-report, based on a cross tab query
to the report. And I could not use the same solution as above to get that to
run. To get that to work, I have to open the subreport (hidden), redefine
the rowsource property and field controlsources, save the subreport, close
it, at 'On Open'.

Not so elegant, and I wonder what effect it has on memory storage, but it
works.

Thanks again, I am sure there are ways I can improve the report's programming.

DEI
 
Back
Top