Crosstab queries and Recordsets in VBA

[Tony]

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",dbOpenDynaset

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

Thank
Ton

 

[Van T. Dinh]

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)

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",dbOpenDynaset)

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.

 

[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

 

[Van T. Dinh]

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

(checked in AXP)

--
HTH
Van T. Dinh
MVP (Access)

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?