programmatically add controls to report

G

G

I had posted previously and received wonderful assistance.
I just have one more question.

I need to programmatically add a % of the report total to
the last available column in my report.

I'm not quite sure where to place the percentage coding.

The last 2 available columns are for the row totals and
the % of report total.

The detail print section goes through each row, adds the
value to the report total, then puts the row total to the
right. Then it goes to the next row.

I don't get a correct report total until it completes the
cycle. So I cannot put it with the detail print coding.
Should I assign the textbox source in the report footer
onprint event?

I'll put the sample and coding below. Thank you in advance
for your assistance.

G

Sample Layout
Total % of Report
9,900 0.00%
500 0.00%

Group total goes here
10,400 0.00%

Report total goes here
10,400 100.00%


The coding for detail section onprint
Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)

Dim intX As Integer
Dim lngRowTotal As Double, dblPct As Double

' If PrintCount is 1, initialize lngRowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0

For intX = 1 To intColumnCount - 4
' Starting at column 2 (first text box with
crosstab value),
' compute total for current row in detail
section.
lngRowTotal = lngRowTotal + Me("txtDet" +
Format$(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX)
+ Me("txtDet" + Format$(intX))
Next intX

' Place row total in text box in detail section.
Me("txtDet" + Format$(intColumnCount - 3)) =
lngRowTotal

'Place the column total in the footer for each
subgrouping
For intX = 1 To intColumnCount
'need to fix the 2nd half of the line below
'Me("txtAssetClassFooter" +
Format$(intColumnCount - 3)) = Me("txtDet" +
Format$(intColumnCount - 3))
Next intX

'Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal

'this procedure is adding the first column's
data to the percentage column.
'Add the percentages to the far right column
End If


End Sub
----------------------
REPORT FOOTER ONPRINT CODING

Private Sub ReportFooter_Print(Cancel As Integer,
PrintCount As Integer)

Dim intX As Integer

' Place column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab
value).
For intX = 1 To intColumnCount - 3
Me("txtGT" + Format$(intX)) = lngRgColumnTotal
(intX)
Next intX

' Place grand total in text box in report footer.
Me("txtGT" + Format$(intColumnCount - 3)) =
lngReportTotal

' Hide unused text boxes in report footer.

For intX = intColumnCount + 2 To conTotalColumns - 3
Me("txtGT" + Format$(intX)).Visible = False
Next intX

End Sub
 
M

Marshall Barton

You can not reliably calculate a total using VBA in either
the Format or Print event procedures. It must be done in
the report's record source query or, when feasible, using
the Sum function in the report header (and/or footer)
sections. The approach you're trying to use probably
wouldn't work even if you could get a value from the end of
the report to appear in the middle of the report.

Try creating a Totals type query to calculate the desired
total and then join that to the original data in a new
record source query.
 
G

G

Thank you Marsh. I have created a Totals query, and will
do as you suggest.
Once again, many thanks.

G

-----Original Message-----
You can not reliably calculate a total using VBA in either
the Format or Print event procedures. It must be done in
the report's record source query or, when feasible, using
the Sum function in the report header (and/or footer)
sections. The approach you're trying to use probably
wouldn't work even if you could get a value from the end of
the report to appear in the middle of the report.

Try creating a Totals type query to calculate the desired
total and then join that to the original data in a new
record source query.
--
Marsh
MVP [MS Access]


I had posted previously and received wonderful assistance.
I just have one more question.

I need to programmatically add a % of the report total to
the last available column in my report.

I'm not quite sure where to place the percentage coding.

The last 2 available columns are for the row totals and
the % of report total.

The detail print section goes through each row, adds the
value to the report total, then puts the row total to the
right. Then it goes to the next row.

I don't get a correct report total until it completes the
cycle. So I cannot put it with the detail print coding.
Should I assign the textbox source in the report footer
onprint event?

I'll put the sample and coding below. Thank you in advance
for your assistance.

G

Sample Layout
Total % of Report
9,900 0.00%
500 0.00%

Group total goes here
10,400 0.00%

Report total goes here
10,400 100.00%


The coding for detail section onprint
Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)

Dim intX As Integer
Dim lngRowTotal As Double, dblPct As Double

' If PrintCount is 1, initialize lngRowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0

For intX = 1 To intColumnCount - 4
' Starting at column 2 (first text box with
crosstab value),
' compute total for current row in detail
section.
lngRowTotal = lngRowTotal + Me("txtDet" +
Format$(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal (intX)
+ Me("txtDet" + Format$(intX))
Next intX

' Place row total in text box in detail section.
Me("txtDet" + Format$(intColumnCount - 3)) =
lngRowTotal

'Place the column total in the footer for each
subgrouping
For intX = 1 To intColumnCount
'need to fix the 2nd half of the line below
'Me("txtAssetClassFooter" +
Format$(intColumnCount - 3)) = Me("txtDet" +
Format$(intColumnCount - 3))
Next intX

'Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal

'this procedure is adding the first column's
data to the percentage column.
'Add the percentages to the far right column
End If


End Sub
----------------------
REPORT FOOTER ONPRINT CODING

Private Sub ReportFooter_Print(Cancel As Integer,
PrintCount As Integer)

Dim intX As Integer

' Place column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab
value).
For intX = 1 To intColumnCount - 3
Me("txtGT" + Format$(intX)) = lngRgColumnTotal
(intX)
Next intX

' Place grand total in text box in report footer.
Me("txtGT" + Format$(intColumnCount - 3)) =
lngReportTotal

' Hide unused text boxes in report footer.

For intX = intColumnCount + 2 To conTotalColumns - 3
Me("txtGT" + Format$(intX)).Visible = False
Next intX

End Sub
Click to expand...

.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Row and Column Averages in Dynamic Crosstab Report? 3
Dynamic Heading in cross tab qry report 3
Error: Microsoft jet database engine does not recognize " as a va 9
Last record of crosstab repeats in report 11
dynamic crosstab report with groupfooter totals 1
count values in report 1
Microsoft Access Dynamic reports 1
Crosstab report repeats last record for all values 10

Top