Crosstab queries and Recordsets in VBA

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

Guest

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
 
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 said:
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
Click to expand...
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
Click to expand...
which is odd since my query takes a few seconds to run.
I can find nothing in the docuomentation, msdn, support forums, etc. which
Click to expand...
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
Click to expand...
open it up in code as above. Please let me know if your Recordset contains
your complete data or just the first record.
 
Back
Top