If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
programmatically add controls to report
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 |
#2
|
|||
|
|||
programmatically add controls to report
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] G wrote: 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 |
#3
|
|||
|
|||
programmatically add controls to report
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] G wrote: 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 . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
programmatically add controls to report | G | Setting Up & Running Reports | 19 | August 26th, 2004 04:32 AM |
Refencing controls in sub report | Joe | Setting Up & Running Reports | 4 | July 15th, 2004 05:03 PM |
Dynamically Move Controls in Report | Andy | Setting Up & Running Reports | 4 | July 14th, 2004 10:47 PM |
Label | SRIT | General Discussion | 2 | June 22nd, 2004 09:42 PM |
Txtbox controls on report that call functions | Stephen | Setting Up & Running Reports | 4 | June 10th, 2004 11:17 PM |