dynamic crosstab report

[Guest]

I've posted a question on form coding, but it's probably the wrong group. At
this point, I'm trying to create a dynamic crosstab report using code I found
in "Fixing Access Annoyances" by Phil Mitchell and Evan Callahan. My first
four column are static, but the next 6 need to be dynamic (e.g., display 1 or
many). Following is the code which gets "stuck" in the following line -
Set rst = CurrentDb.OpenRecordset(Me.RecordSource).
Don't know if the rest would work, because I don't get very far. Can anyone
please help:

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim intFields As Integer
Dim intControls As Integer
Dim N As Integer
'Open a recordset for the crosstab query.
Set rst = CurrentDb.OpenRecordset(Me.RecordSource)

' Find the number of text boxes available in the Detail section, minus 4
because we don't count the first 4 row header controls.
intControls = Me.Detail.Controls.Count - 4
' Find the number of fields, minus 4, because we don't count the first 4 row
header filed.
intFields = rst.Fields.Count - 4
' We can't use more than intControls number of fields.
If intFields > intControls Then
intFields = intControls
End If
' Iterate through report fields to set label captions and field control
sources.
For N = 1 To intControls
If N <= intFields Then
Me.Controls("Label" & N).Caption = rst.Fields(N).Name
Me.Controls("Field" & N).ControlSource = rst.Fields(N).Name
Else
'Hide extra controls.
Me.Controls("Label" & N).Visible = False
Me.Controls("Field" & N).Visible = False
End If
Next N
rst.Close
End Sub

I really need help. Once I have the dynamic report, I'll go back to the
Multiselect list box questions which also gives me trouble. I'm in way over
my head and need help.
Thanks.

 

[Guest]

Hi Brigitte,

You are referring to me.recordsouce that assumes that you have placed a
crosstabquery in your report. If you haven't placed a recordsource the rst
doesn't know what to open.

 

[krissco]

I've posted a question on form coding, but it's probably the wrong group. At
this point, I'm trying to create a dynamic crosstab report using code I found
in "Fixing Access Annoyances" by Phil Mitchell and Evan Callahan. My first
four column are static, but the next 6 need to be dynamic (e.g., display 1 or
many). Following is the code which gets "stuck" in the following line -
Set rst = CurrentDb.OpenRecordset(Me.RecordSource).
Don't know if the rest would work, because I don't get very far. Can anyone
please help:

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim intFields As Integer
Dim intControls As Integer
Dim N As Integer
'Open a recordset for the crosstab query.
Set rst = CurrentDb.OpenRecordset(Me.RecordSource)

' Find the number of text boxes available in the Detail section, minus 4
because we don't count the first 4 row header controls.
intControls = Me.Detail.Controls.Count - 4
' Find the number of fields, minus 4, because we don't count the first 4 row
header filed.
intFields = rst.Fields.Count - 4
' We can't use more than intControls number of fields.
If intFields > intControls Then
intFields = intControls
End If
' Iterate through report fields to set label captions and field control
sources.
For N = 1 To intControls
If N <= intFields Then
Me.Controls("Label" & N).Caption = rst.Fields(N).Name
Me.Controls("Field" & N).ControlSource = rst.Fields(N).Name
Else
'Hide extra controls.
Me.Controls("Label" & N).Visible = False
Me.Controls("Field" & N).Visible = False
End If
Next N
rst.Close
End Sub

I really need help. Once I have the dynamic report, I'll go back to the
Multiselect list box questions which also gives me trouble. I'm in way over
my head and need help.
Thanks.

Add this line before the line that crashes:
Debug.Print Me.RecordSource

Please post what your recordsource is (query, table, SQL string) as
how you would open it from code depends on this.

-Kris

 

[Guest]

The debug line results in
qryEventsReportCard_Crosstab
which is the right record source listed in the property window as
RecordSource for the report.

The crosstab runs just fine when I run it from the query window.

 

[Guest]

Here is the SQL of my underlying query since this seems to be the source of
the bug:
PARAMETERS [Forms]![frmMultiselect]![txtUnit] Text ( 255 ),
[Forms]![frmMultiselect]![txtEndDate] DateTime,
[Forms]![frmMultiselect]![txtStartDate] DateTime;
TRANSFORM Count(qryEventsReportCard.EventEvent) AS CountOfEventEvent
SELECT qryEventsReportCard.EventEvent, [SumOfSix]/6 AS [6 Mo Avg],
[SumOfTwelve]/12 AS [12 Mo Avg], qryEventsReportCard.EventBuilding,
Count(qryEventsReportCard.EventEvent) AS [Total Of EventEvent]
FROM qryEventsReportCard INNER JOIN qryEventReportCard12_6Sum ON
(qryEventReportCard12_6Sum.EventBuilding = qryEventsReportCard.EventBuilding)
AND (qryEventsReportCard.EventEvent = qryEventReportCard12_6Sum.EventEvent)
WHERE (((qryEventsReportCard.EventDate) Between
[Forms]![frmMultiselect]![txtStartDate] And
[Forms]![frmMultiselect]![txtEndDate]))
GROUP BY qryEventsReportCard.EventEvent, [SumOfSix]/6, [SumOfTwelve]/12,
qryEventsReportCard.EventBuilding
PIVOT qryEventsReportCard.MonthNumber;

I have four static fields

 

[krissco]

PARAMETERS [Forms]![frmMultiselect]![txtUnit] Text ( 255 ),

[Forms]![frmMultiselect]![txtEndDate] DateTime,
[Forms]![frmMultiselect]![txtStartDate] DateTime;

I'm not too familiar with DAO but I know that this would bomb in ADO.
The problem is the parameters.

Comment out these two lines:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(Me.RecordSource)

Add in this stuff instead:
'ADO objects for dealing w/ parameters in queries
Dim rst As ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param1 As String, param2 As Date, param3 As Date

'Retreive params into variables (easier on the eyes)
param1 = [Forms]![frmMultiselect]![txtUnit]
param2 = [Forms]![frmMultiselect]![txtEndDate]
param3 = [Forms]![frmMultiselect]![txtStartDate]

'Setup the command object
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "Select * from " & Me.RecordSource
cmd.Parameters.Append cmd.CreateParameter("FirstParam", adDate,
adParamInput, Len(param1), param1)
cmd.Parameters.Append cmd.CreateParameter("SecondParam", adDate,
adParamInput, , param2)
cmd.Parameters.Append cmd.CreateParameter("ThirdParam", adDate,
adParamInput, , param3)

'Get the recordset
Set rst = cmd.Execute()

Make sure you have a reference to ADO. You will want to add some
cleanup code for the new objects too. I'm sure that this can be done
w/ DAO - I'm just not very familiar w/ that technology.

-Kris

 

[krissco]

Oops. I just noticed a type-o. The first adDate should be adChar

cmd.Parameters.Append cmd.CreateParameter("FirstParam", adDate,
adParamInput, Len(param1), param1)

-Kris

 

[Guest]

Thanks for trying to help. I don't know much about DAO and don't have ADO
available in my library selection. I tried just to include your
recommendations into my existing code which, as expected, didn't work in the
DAO version. Why the thing doesn't accept my query as a recordsource
obviously has to do with the parameters, and Microsoft KB seems to have a
solution that also didn't work for me. They try to accomodate so many
possiblities that I got lost. It seems like those dynamic crosstabs reports
puzzle many lay developers like me; I'll keep on searching. If all else
fails, I let my user dump the query results into Excell and do a macro in
Excel to write the report. Clunky, but maybe the only way I know how to do
this. I'm telling my facility for a long time that they should hire a
professional, maybe they listen to me this time Thanks for your efforts.
Brigitte P.