Dynamic crosstab report - need help!!

[Tanya]

Oh boy! Some of this over my head but I think I'm very
close to the solution I need. I got this code from the
Microsoft article #328320. In the Format_Detail event of
the report I see how hiding unused text boxes is possible.
Which is what I need. I think I have a simple problem of
not counting my fields properly. The report runs but the
values are not being put into the text boxes on the report.
There are 13 text boxes and the first 7 are static fields
from the crosstab query. I have the last 6 named ShowDate8
to ShowDate13 and unbound text boxes in the page header
named lblheader8 to lblheader13.

code:
Option Compare Database
Const conTotalColumns = 13
Dim dbsREport As DAO.Database
Dim rstReport As DAO.Recordset
Dim intColumnCount As Integer

Private Function xtabCnulls(varX As Variant)
'Test if 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 Detail_Format(Cancel As Integer, FormatCount As
Integer)
'Put values in text boxes and hide unused text boxes.

Dim intX As Integer
'Verify that you are not at the end of recordset.
If Not rstReport.EOF Then
'If FormatCount is 1, put values from recordset into
text boxes in Detail section.
If Me.FormatCount = 1 Then
For intX = 8 To intColumnCount
'Convert Null values to 0.
Me("ShowDate" + Format(intX)) =
xtabCnulls(rstReport(intX))
Next intX

'Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 8 To conTotalColumns
Me("ShowDate" + Format(intX)).Visible = False
Next intX

'Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub

Private Sub Detail_Retreat()
'Always back up to previous when "Detail" section retreats.
rstReport.MovePrevious
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer,
FormatCount As Integer)


Dim intX As Integer
'Put column headings into text boxes in page header.
For intX = 8 To intColumnsCount
Me("lblHeader" + Format(intX)) = rstReport(intX - 1).Name
Next intX

'Hide unused text boxes in page header.
For intX = intColumnCount + 8 To conTotalColumns
Me("lblHeader" + Format(intX)).Visible = False
Next intX

End Sub

Private Sub Report_Close()
On Error Resume Next

'Close recordset.
rstReport.Close

End Sub

Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records match the dates you entered.",
vbExclamation, "No Records Found"
rstReport.Close
Cancel = True

End Sub



Private Sub Report_Open(Cancel As Integer)
'Create underlying recordset for report using criteria
entered in frmMasterReportFilter form

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

'Set database variable to current database
Set dbsREport = CurrentDb
Set frm = Forms!frmMasterReportFilter
'Open Querydef Object
Set qdf = dbsREport.QueryDefs(Me.RecordSource)
'Set parameters for query based on values entered in
frmMasterReportFilter form.
qdf.Parameters(0) _
= frm!StartDate
qdf.Parameters(1) _
= frm!EndDate

'Open Recordset Object
Set rstReport = qdf.OpenRecordset()

'set a variable to hold number of columns in crosstab query.
intcolumns = rstReport.Fields.Count

End Sub



Thanks so much for any help!!

Tanya

 

[Duane Hookom]

I don't care for the knowledge base solution. There are more flexible
solutions in samples found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. If your last 6 are a
date increment then there are much better methods.

 

[Guest]

-----Original Message-----
Oh boy! Some of this over my head but I think I'm very
close to the solution I need. I got this code from the
Microsoft article #328320. In the Format_Detail event of
the report I see how hiding unused text boxes is possible.
Which is what I need. I think I have a simple problem of
not counting my fields properly. The report runs but the
values are not being put into the text boxes on the report.
There are 13 text boxes and the first 7 are static fields
from the crosstab query. I have the last 6 named ShowDate8
to ShowDate13 and unbound text boxes in the page header
named lblheader8 to lblheader13.

code:
Option Compare Database
Const conTotalColumns = 13
Dim dbsREport As DAO.Database
Dim rstReport As DAO.Recordset
Dim intColumnCount As Integer

Private Function xtabCnulls(varX As Variant)
'Test if 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 Detail_Format(Cancel As Integer, FormatCount As
Integer)
'Put values in text boxes and hide unused text boxes.

Dim intX As Integer
'Verify that you are not at the end of recordset.
If Not rstReport.EOF Then
'If FormatCount is 1, put values from recordset into
text boxes in Detail section.
If Me.FormatCount = 1 Then
For intX = 8 To intColumnCount
'Convert Null values to 0.
Me("ShowDate" + Format(intX)) =
xtabCnulls(rstReport(intX))
Next intX

'Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 8 To conTotalColumns
Me("ShowDate" + Format(intX)).Visible = False
Next intX

'Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub

Private Sub Detail_Retreat()
'Always back up to previous when "Detail" section retreats.
rstReport.MovePrevious
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer,
FormatCount As Integer)


Dim intX As Integer
'Put column headings into text boxes in page header.
For intX = 8 To intColumnsCount
Me("lblHeader" + Format(intX)) = rstReport(intX - 1).Name
Next intX

'Hide unused text boxes in page header.
For intX = intColumnCount + 8 To conTotalColumns
Me("lblHeader" + Format(intX)).Visible = False
Next intX

End Sub

Private Sub Report_Close()
On Error Resume Next

'Close recordset.
rstReport.Close

End Sub

Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records match the dates you entered.",
vbExclamation, "No Records Found"
rstReport.Close
Cancel = True

End Sub



Private Sub Report_Open(Cancel As Integer)
'Create underlying recordset for report using criteria
entered in frmMasterReportFilter form

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

'Set database variable to current database
Set dbsREport = CurrentDb
Set frm = Forms!frmMasterReportFilter
'Open Querydef Object
Set qdf = dbsREport.QueryDefs(Me.RecordSource)
'Set parameters for query based on values entered in
frmMasterReportFilter form.
qdf.Parameters(0) _
= frm!StartDate
qdf.Parameters(1) _
= frm!EndDate

'Open Recordset Object
Set rstReport = qdf.OpenRecordset()

'set a variable to hold number of columns in crosstab query.
intcolumns = rstReport.Fields.Count

End Sub



Thanks so much for any help!!

Tanya
.

Hi Tanya
I've got almost all same type of problem which you are
facing. I refere the knowledgebase article from Microsoft
and created simple report which will analyse different
types of products deliver from store against delivery
number. In rowfield all Issue Nos are there and in columns
my products are there, when I run this report I'll get
only six columns, but I've defined 13 constant columns.
If you want to see my file I can fax to you?
Please help me in this regard, if this problem is solved
than I can remove complete Microsoft Excel from my
computer.
Thanking you in advance
(e-mail address removed)