Solved

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

Posted on 2007-11-19
8
241 Views
Last Modified: 2011-10-03
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
Comment
Question by:cford31
  • 4
  • 3
8 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 20313618
create the textboxes and column headers dynamically.  See this link:
                           http://support.microsoft.com/kb/328320

0
 

Author Comment

by:cford31
ID: 20313893
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 20314043
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
 

Author Comment

by:cford31
ID: 20315048
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20317894
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
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 20319435
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
 

Author Closing Comment

by:cford31
ID: 31409958
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
 

Author Comment

by:cford31
ID: 20320682
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sending email from List Data 2 51
ms/access hyperlink/ftp 7 39
VBA code won't run Delete Query 5 26
error 3022 in access vba 3 14
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now