• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

How do I create a crosstab report that expands both horizontally and vertically?

I have a crosstab report that has 30 label boxes as headers and 30 text boxes for the data in the detail section. These are looped through and set when the report is opened. The problem is if there are more than 30 columns generated by the crosstab query then they are not on the report.

I have struggled with a way to do this.
1) I thought of possibly opening the report and then reopening it after it is closed and generating the rest of the columns. This is confusing to the user if they look at it in print preview mode b/c not all of the data is there.

2) I could insert another 30 column labels but what if there are more than 60 columns?

What I would like is to have the report reuse the first 30 columns for 31-60. This way it would be possible to have an unlimited amount of columns.

Private Sub Report_Open(Cancel As Integer)
    Dim rst As DAO.Recordset
    Dim iCount As Integer
    Dim fld As Field
On Error GoTo Err_ErrorTag
    
    iCount = 1
    Set rst = CurrentDb.OpenRecordset("qryTimesheet_GroupByEmployee_ThenActivity")
    
    For Each fld In rst.Fields
        '// These are the Rows. We want to start with the Column Headers.
        If fld.Name <> "Employee" And fld.Name <> "Employee_EmpID" And fld.Name <> "TotalOfTimeTotal" Then
                 
            Me.Controls("txt" & iCount).ControlSource = "=([" & fld.Name & "]/[TotalOfTimeTotal])*100"
            Me.Controls("lbl" & iCount).Caption = fld.Name
            
            iCount = iCount + 1
            
            '// This was added because only 30 controls fit on the report. However there are more columns being left out.
            If iCount > 30 Then
                Exit For    '// Exit For Next
            End If
         
        End If
      
    Next
 
    '// This hides the controls if there are less then 30.
    Do Until iCount > 30
        Me.Controls("txt" & iCount).Visible = False
        Me.Controls("lbl" & iCount).Visible = False
        Me.Controls("lbl" * iCount & "a").Visible = False
        Me.Controls("line" & iCount).Visible = False
        iCount = iCount + 1
    Loop
 
Exit_ErrorTag:
    On Error Resume Next
    Exit Sub
 
Err_ErrorTag:
    MsgBox Err.Description & vbCrLf & vbCrLf & "     Error #:   " & Err.Number & vbCrLf & "Error Line:   " & Erl & vbCrLf & "Procedure:   Report_Open " & vbCrLf & "   Location:  Report_rptTimesheet_EmployeeActivity_EmployeTotals", vbExclamation, "Error"
    Resume Exit_ErrorTag
 
End Sub

Open in new window

0
cford31
Asked:
cford31
  • 4
  • 3
1 Solution
 
puppydogbuddyCommented:
create the textboxes and column headers dynamically.  See this link:
                           http://support.microsoft.com/kb/328320

0
 
cford31Author Commented:
Puppydog,

This is what I am already doing. It requires you to setup a maximum amount of columns. I was trying to think of a different way to do this.

I forgot to mention this in my original post but this is for a mde.
0
 
puppydogbuddyCommented:
if it is for an mde, how are you going to access the source code???

your query will return the number of dynamic columns to the report open via the .Fields.Count method for your recordset.  Use that to determine how many dynamic textboxes to create.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
cford31Author Commented:
I will be able to "access my source code" while I'm developing the database but it will be distributed as a mde.

Did you look at my code? I am already looping through the fields and you can't "create" a textbox programmically unless you are in design view. (which is not possible b/c this will be a mde.)
0
 
Jeffrey CoachmanMIS LiasonCommented:
cford31,

If this Report is based off of an actual CrossTab query, then why not create a Report from it using the Report Wizard?

I've done it a few time and it does not look bad.

...might be simpler than the VBA approach.

JeffCoachman
0
 
puppydogbuddyCommented:
Realistically you are going to have to establish some maximum limit on the number of columns.  But, see this link for an idea that involves report selection based on the number of "logical" pages required.   Be sure and read the final post at this link.

                                http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21450286.html
0
 
cford31Author Commented:
That did it! Thanks puppydogbuddy!

I was able to solve my problem by creating 2 different reports. 1 report that has 30 fields on it and a seperate report that has 60 fields with the column size set to 20.8" (Landscape) under Page Setup. I will use a dialog box for the user to open the report and if there are more than 30 columns needed then it will open the second report.
0
 
cford31Author Commented:
That did it! Thanks puppydogbuddy!

I was able to solve my problem by creating 2 different reports. 1 report that has 30 fields on it and a seperate report that has 60 fields with the column size set to 20.8" (Landscape) under Page Setup. I will use a dialog box for the user to open the report and if there are more than 30 columns needed then it will open the second report.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now