Crosstab queries and Recordsets in VBA

Hi all,

I have an interesting issues trying to create a Recordset from a Crosstab query.

I am making a report with some graphs and I want to populate the graphs with data from a crosstab query which I have already defined and know works. There are no parameters involved.

When I open the report to populate the graphs, I create a recordset as follows:

Set qdef = CurrentDb.QueryDefs("MyCrosstab")
Set rst = qdef.OpenRecordset(dbOpenDynaset)

or as

Set rst=CurrentDb.OpenRecordset("MyCrosstab",dbOpenDyn aset)

While I get a recordset, it only has 1 record. It also returns immediately which is odd since my query takes a few seconds to run.

I can find nothing in the docuomentation, msdn, support forums, etc. which explains this behavior. I have also tried various type permutations of OpenRecordset, as well as moving this functionality out of the report object and into a module.

I can't believe that something this fundamental would be a bug, but I am beginning to wonder.

To duplicate the problem, create any normal and simple crosstab query and open it up in code as above. Please let me know if your Recordset contains your complete data or just the first record.

Thanks
Tony

Usually, when you first open a Recordset, it is not fully populated and will
show the RecordCount of 1. You need to do a MoveLast before obtaining the
value of RecordCount to get the accurate RecordCount.

--
HTH
Van T. Dinh
MVP (Access)



"Tony" wrote in message
...
Hi all,

I have an interesting issues trying to create a Recordset from a Crosstab
query.

I am making a report with some graphs and I want to populate the graphs
with data from a crosstab query which I have already defined and know works.
There are no parameters involved.

When I open the report to populate the graphs, I create a recordset as
follows:

Set qdef = CurrentDb.QueryDefs("MyCrosstab")
Set rst = qdef.OpenRecordset(dbOpenDynaset)

or as

Set rst=CurrentDb.OpenRecordset("MyCrosstab",dbOpenDyn aset)

While I get a recordset, it only has 1 record. It also returns immediately
which is odd since my query takes a few seconds to run.

I can find nothing in the docuomentation, msdn, support forums, etc. which
explains this behavior. I have also tried various type permutations of
OpenRecordset, as well as moving this functionality out of the report object
and into a module.

I can't believe that something this fundamental would be a bug, but I am
beginning to wonder.

To duplicate the problem, create any normal and simple crosstab query and
open it up in code as above. Please let me know if your Recordset contains
your complete data or just the first record.

Thanks
Tony

Hi Van,

Thank you. I am very thankful for your answer. It worked as you said.

However my curious analytical side is now asking questions. Is this normal procedure? Where is this documented? Of all my dealings with recordsets, and looking at examples, this is the first time I have run accross this. I am therefore curious as to what else I don't know about recordsets. Do you know of any good references?

Thanks again,
Tony

It is in Access VB Help topic "RecordCount Property" (for DAO Recordset).

(checked in AXP)

--
HTH
Van T. Dinh
MVP (Access)


"Tony" wrote in message
...
Hi Van,

Thank you. I am very thankful for your answer. It worked as you said.

However my curious analytical side is now asking questions. Is this
normal procedure? Where is this documented? Of all my dealings with
recordsets, and looking at examples, this is the first time I have run
accross this. I am therefore curious as to what else I don't know about
recordsets. Do you know of any good references?

Thanks again,
Tony