-----Original Message-----
I don't care much for the knowledge base solution. There
is a much more
flexible solution demonstrated in a download from
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.
--
Duane Hookom
MS Access MVP
Hi,
with the help of knowledge base I created report based
on
Crosstab query. I think all the codes and declartions I
did properly, when I run the report and if columns are
less than or equal to six then this report run properly
with column total it is showing but when columns are
more
than six than it will will only six columns?
I declared my constant columns equal to 13, Please help
me
in this regards.
Thanks in advance
Wahab
.
Hello, Mr. Duane Hookom
I tried your report its working fine, but I got some
specific report which require the same report shown in MS
Knowledgebase. Another thing knowledgebase solution also
good, you simply can't keep aside. Any how I spend lot of
time to get that report, but problem remain as it is. I
tried the solution article; there also columns are coming
only 6 with me. Please analyze my code and check why only
six columns are coming. Hope you will not disappoints me.
========
Option Compare Database
Option Explicit
' Constant number of columns Issue Checking Rolls query
would
Const conTotalColumns = 13
' Variables for Database object and Recordset.
Dim dbsreport As DAO.Database
Dim rstReport As DAO.Recordset
' Variables for number of columns and row and report
totals.
Dim intColumnCount As Integer
Dim dblRgColumnTotal(1 To conTotalColumns) As Double
Dim dblReportTotal As Double
================
Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
' Place values in text boxes and hide unused text boxes.
Dim intX As Integer
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset into
text boxes
' in detail section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls
(rstReport(intX - 1))
Next intX
' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 To
conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
End If
End Sub
====================
Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)
Dim intX As Integer
Dim dblRowTotal As Double
' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
dblRowTotal = 0
For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab
value),
' compute total for current row in detail section.
dblRowTotal = lngRowTotal + Me("Col" + Format
(intX))
' Add crosstab value to total for current column.
dblRgColumnTotal(intX) = dblRgColumnTotal
(intX) + Me("Col" + Format(intX))
Next intX
' Place row total in text box in detail section.
Me("Col" + Format(intColumnCount + 1)) =
lngRowTotal
' Add row total for current row to grand total.
dblReportTotal = dblReportTotal + lngRowTotal
End If
End Sub
=================
Private Sub Detail_Retreat()
' Always back up to previous record when detail section
retreats.
rstReport.MovePrevious
End Sub
================
Private Sub InitVars()
Dim intX As Integer
' Initialize dblReportTotal variable.
dblReportTotal = 0
' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
dblRgColumnTotal(intX) = 0
Next intX
End Sub
==============
Private Sub PageHeader_Format(Cancel As Integer,
FormatCount As Integer)
Dim intX As Integer
' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX -
1).Name
Next intX
' Make next available text box Totals heading.
Me("Head" + Format(intColumnCount + 1)) = "Totals"
' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX
End Sub
============
Private Sub Report_Close()
On Error Resume Next
rstReport.Close
End Sub
===============
Private Sub Report_Open(Cancel As Integer)
' Create underlying recordset for report using criteria
entered on form
Dim intX As Integer
Dim qdf As QueryDef
' Don't open report if particulart form is not open/
loaded.
If Not (IsLoaded("Other Dialog Form")) Then
Cancel = True
MsgBox "To preview or print this report, you must
open " _
& "Other Dialog Form, in Form view.",
vbExclamation, _
"Must Open Dialog Box"
Exit Sub
End If
' Set database variable to current database.
Set dbsReport = CurrentDb
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Issue Checking Rolls")
' Set parameters for query based on values entered
qdf.Parameters("Forms![Other Dialog Form]!
BeginningDate") = _
[Forms]![Other Dialog Form]!BeginningDate
qdf.Parameters("Forms![Other Dialog Form]! EndingDate")
= _
[Forms]![Other Dialog Form]!EndingDate
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()
' Set a variable to hold number of columns in crosstab
query.
intColumnCount = rstReport.Fields.Count
End Sub
===============
Private Sub ReportFooter_Print(Cancel As Integer,
PrintCount As Integer)
Dim intX As Integer
' Place column totals in text boxes in report footer.
For intX = 2 To intColumnCount
Me("Tot" + Format(intX)) = dblRgColumnTotal (intX)
Next intX
' Place grand total in text box in report footer.
Me("Tot" + Format(intColumnCount + 1)) = dblReportTotal
' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("Tot" + Format(intX)).Visible = False
Next intX
End Sub
================
Private Function xtabCnulls(varX As Variant)
' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If
End Function
=================
Private Sub ReportHeader_Format(Cancel As Integer,
FormatCount As Integer)
' Move to first record in recordset at beginning of report
rstReport.MoveFirst
'Initialize variables.
InitVars
End Sub
Thanking you in advance
Wahab Rajiwate
(e-mail address removed)
