grouping when report is bound using VBA

[belovedcej]

I have a database in which the recordsource for reports are set using VBA, not the properties.

So on the onOpen event of the report I set the recordset.
On the onFormat Event of the report header I set the values of any calculated controls which will be in the report header.

This is fine for the majority of my reports, which come in sorted from the database anyway.

But on two reports, I have extensive grouping as well as sorting and I get the message that there is no data for the report (my error handling on the noData event).

I tried the following on both the Open Event and the format event:

Set rst = CurrentProject.Connection.Execute _
(g_tablePrefix & "RPT_Statistics_SP ('" & g_parameter1 & "', '" & g_parameter2 & "')")

Me.GroupLevel(0).ControlSource = rst.Fields("Type")
Me.GroupLevel(1).ControlSource = rst.Fields("Grouped")
Me.GroupLevel(2).ControlSource = rst.Fields("Category")

this still gives me the message "no data".

I think the problem is that it is only reading the first value of each column into the variable instead of knowing it should be the whole column. But I don't see a property for the ADO recordset which allows me to refer to the whole column instead of the fields.

Does anyone have any ideas for this?

 

[seaport]

My guess is that you are using Access 2003 and you dynamically set the recordset source of the report. So
you may try the following code in the form_open event.
{code]
Set rst = CurrentProject.Connection.Execute _
(g_tablePrefix & "RPT_Statistics_SP ('" & g_parameter1 & "', '" & g_parameter2 & "')")
set me.recordset=rst
Me.GroupLevel(0).ControlSource = "Type"
Me.GroupLevel(1).ControlSource = "Grouped"
Me.GroupLevel(2).ControlSource = "Category"
[/code]

I donot think you can set a report's recordset in Access 2000.

Hope this helps.

Seaport

 

[belovedcej]

Thanks for the suggestion. Tried that one.

It didn't work either.

But yes, it is Access 2003 and I am dynamically setting the recordset.