Dynamic Crosstab Report with Totals

[CD]

A2K

I have a simple report that shows a list of drawings and the dates we
received them as well as the Revision # of that drawing. In the
crosstab "Revision#" is the col and Drawing is the Row with
DateReceived being the value. Since I never know what the greatest
revision # I have received, I made the report dynamic with the
following code.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Report_Open_Error
Dim X As Integer
Dim Y As Integer
Dim numFields As Integer
Dim strLabel As String
Dim strFieldName As String
Dim strValue As String
Dim Z As Integer
Dim db As Database
Set db = CurrentDb



' Determine how many columns of data you will have
Y = db.QueryDefs("qryProgress_Iso_RevHistory").Fields.Count - 4

' Loop through Fields and set labels equal to Rev # and text box
equal to Date Iso Received
For X = 1 To Y
Z = X + 3
strLabel = "lbl" & X
strValue = "txtIso" & X
strFieldName =
db.QueryDefs("qryProgress_Iso_RevHistory").Fields(Z).Name
Me.Controls(strLabel).Caption = strFieldName
Me.Controls(strLabel).Visible = True
Me.Controls(strValue).ControlSource = strFieldName
Me.Controls(strValue).Visible = True
Next

Report_Open_Exit:
Exit Sub

Report_Open_Error:
MsgBox Err.Number & " " & Err.Description
GoTo Report_Open_Exit

End Sub

The code works fine. However I find that I need to have some totals.
So for Revision#1 we have 123 drawings and Revision #2 we have 32
drawing, etc. But I cannot seem to get anything to work. I am unable
to get any totals in the footers with unbound text boxes.

Does anybody have any suggestions on how to do this with code?

Thanks,

Charles D Clayton Jr

 

[George Nicholson]

(Untested aircode)

Following:
Me.Controls(strValue).ControlSource = strFieldName
Me.Controls(strValue).Visible = True
Try adding something like:
Me.Controls(strValue & "GT").ControlSource = " = Sum([" & strFieldName &
"])"
Me.Controls(strValue & "GT").Visible = True
(Assuming you have added textboxes txtIso1GT, txtIso2GT, etc. to your
report..)

HTH,

 

[CD]

Thanks for the suggestion but I am getting an error. It says "Syntax
Error in query Expression 'First([=Sum([0])])' I am not really sure
why that error is coming up. When I put a msgbox Me.Controls(strValue
& "GT").ControlSource = " = Sum([" & strFieldName &
"])" it gives me the value "=Sum([0])" which is correct.

I realized that the data consists of "dates" which caused me to change
the syntax from Sum to Count but it still does not work.

Since the totals are calculated in the footer, I tried putting the code
for that section in the "On Print" and "On Format" but it would not
work.

Do you have any other suggestions?

Thanks,

Charles D Clayton Jr

 

[Duane Hookom]

Did you try put the code in the same place (Report Open)?

There are several crosstab query report samples at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The Crosstab.mdb might
have a working solution.

 

[CD]

I did put it in the same place (Report Open). I only moved it because
it gave me the error and I thought maybe something was wrong on the
order of events. Unfortunately, that did not fix anything. I have
looked at your material in the past and used it before for other
reports but it did not seem necessary for this one as it was so simple.

Thanks,

Charles D Clayton Jr

 

[CD]

I did finally find a solution. If I changed the code to this, it
worked fine.
Me(strValue & "GT").ControlSource = "= Count([" & strFieldName
& "])"
Me(strValue & "GT").Visible = True

Thanks for all the help

 

[CD]

I did finally find a solution. If I changed the code to this, it
worked fine.
Me(strValue & "GT").ControlSource = "= Count([" & strFieldName
& "])"
Me(strValue & "GT").Visible = True

Thanks for all the help