crosstab report question 2

[elvenmaiden]

I am hard coding a crosstab query report that has a variable number of columns up to 16. The problem is I can only seem to print the first or last record in the recordset to the report print preview. (There is a looping structure I tried, but it is now commented out - it shows only the last record in the recordset).

Would appreciate any suggesions. Below is the code so far:


Option Compare Database
Option Explicit

'dimensions connection and recordset
Dim CurConn As New ADODB.Connection
Dim rsSalesHours As New ADODB.Recordset

Dim mintColumnCount As Integer 'holds count of columns generated by cross tab query
Dim mintColumnTotals(12) As Integer 'array to hold column totals
Dim mintDifference As Integer 'holds difference between columns selected
Dim mintTotalDifference As Integer 'holds total difference for the report
Dim mintTotal As Integer 'holds total of all columns over entire time span

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

Dim intX As Integer

'verify that not at end of recordset
If Not rsSalesHours.EOF Then

'ensures FormatCount is not showing hold for next page
If Me.FormatCount = 1 Then

'Do Until rsSalesHours.EOF

'fill detail text boxes
For intX = 1 To mintColumnCount
Me("Detail" + Format$(intX)) = rsSalesHours(intX - 1)

Next intX

'hide unused text boxes in detail section
For intX = (mintColumnCount + 2) To 16
Me("Detail" + Format$(intX)).Visible = False
Next intX


'move to next record
rsSalesHours.MoveNext

'Loop

End If

End If

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 = 1 To mintColumnCount
Me("Head" + Format$(intX)) = rsSalesHours(intX - 1).Name
Next intX

'make next available text box Difference heading.
Me("Head" + Format$(mintColumnCount + 1)) = "Difference"

'Hide unused text boxes in page header.
For intX = (mintColumnCount + 2) To 16
Me("Head" + Format$(intX)).Visible = False
Next intX

End Sub

Private Sub Report_Open(Cancel As Integer)

'Open Recordset for use
rsSalesHours.ActiveConnection = CurrentProject.Connection
rsSalesHours.CursorType = adOpenStatic
rsSalesHours.Open "SELECT * FROM qryMonthSalesHours"

'determine number of columns in the recordsource
mintColumnCount = rsSalesHours.Fields.Count

End Sub

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

'moves to first record in recordset
rsSalesHours.MoveFirst

'calls variable initialization routing
Call InitializeVariables

End Sub

Public Sub InitializeVariables()

Dim intCounter As Integer

'initialize array to store horizontal column totals
For intCounter = 0 To 12
mintColumnTotals(intCounter) = 0
Next intCounter

'initializes mintDifference to hold column difference
mintDifference = 0

'initializes mintTotalDifference to hold grand total of difference
mintTotalDifference = 0

'initalizes mintTotal to hold grand total of all columns
mintTotal = 0

End Sub

 

[raskew]

From all appearances, your code is an adaptation of that contained in the Employee Sales report in the Solutions database. It's intended to create dynamic column headings in a report, and hide the ones which aren't used.

So, when you say you're '...hard-coding a crosstab query...', I'm really confused.

Little more info would be helpful.

 

[elvenmaiden]

I didn't realize this was in the solutions database...

Got the structure from a book.

Have several cross-tabs queries that will feed into unbound reporting structure with the above code running in the background.

The code I have listed is working for dynamic column headings - no problem there.

The part that doesn't seem to be working correctly is in the detail section. The recordset I am coming from has over 100 records and I only get 1 record in the detail. Either the 1st record in the recordset using the code without a do loop or the last record if I put in a do loop.

Thanks for any help!

 

[VBslammer]

You should be able to accomplish what you're trying with a lot less work. Ken Getz provides a nice sample of a crosstab report in the Access Developer's Handbook that handles everything in the Report_Open() event. It 'binds' the necessary controls as well as hides unused controls.

Private Sub Report_Open(Cancel As Integer)
    ' You didn't know how many columns, or what
    ' their names would be, until now.
    ' Fill in the label captions,
    ' and control ControlSources.
    
    Dim intColCount As Integer
    Dim intControlCount As Integer
    Dim i As Integer
    Dim strName As String
    
    On Error Resume Next
    
    Dim rst As ADODB.Recordset
    
    Set rst = New ADODB.Recordset
    rst.Open _
     Source:=Me.RecordSource, _
     ActiveConnection:=CurrentProject.Connection, _
     Options:=adCmdTable
    
    intColCount = rst.Fields.Count
    intControlCount = Me.Detail.Controls.Count
    
    If intControlCount < intColCount Then
        intColCount = intControlCount
    End If
    
    ' Fill in information for the necessary controls.
    For i = 1 To intColCount
        strName = rst.Fields(i - 1).Name
        Me.Controls(&quot;lblHeader&quot; & i).Caption = strName
        Me.Controls(&quot;txtData&quot; & i).ControlSource = strName
        Me.Controls(&quot;txtSum&quot; & i).ControlSource = _
         &quot;=Sum([&quot; & strName & &quot;])&quot;
    Next i
    
    ' Hide the extra controls.
    For i = intColCount + 1 To intControlCount
        Me.Controls(&quot;txtData&quot; & i).Visible = False
        Me.Controls(&quot;lblHeader&quot; & i).Visible = False
        Me.Controls(&quot;txtSum&quot; & i).Visible = False
    Next i
    
    ' Close the recordset.
    rst.Close
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[elvenmaiden]

VBSlammer

thanks for the help. When I used this code I get a 9-page report preview - but there isn't any records showing (i.e. the 9-pages are blank).

I feel really stupid, but what do you think I am doing wrong?

 

[elvenmaiden]

Thanks, I had my recordsourse messed up. So...it's running now. Thanks a million!!!!