report is wider than the page

[Davit]

I have a crosstab report that generates through vba code. The data columns are variable and the names of columns and their number changes every time. Very often the report size is wider than the page size, even with the landscape layout. When that happens the page numbers and total amount of pages are incorrect. The additional pages are being ignored in calculation of [Page] and [Pages]. I tried to play with page and format events of different section, but couldn't figure out how to print correct page numbers on every page. Ideally I would like to also repeat some row headers too, but the page numbers are more important. Please advise, thank you!

[twinnyfo]

Davit,

This is one of the challenges when using Cross Tab queries for reports. The biggest challenge, as you described, is that the column count can change--thus also making it impossible to use as a standard record source for a report.

I'm not sure I have a solution for your problem, but you may want to consider other options for reporting, such that there are standard fields returned.

For example, lets say you have a cross tab query that returns the past 12 months' worth of data, with each month being a field. So, your columns would be "Nov-13", "Dec-13", "Jan-13"..."Oct-14". You create a report that uses each of these field names--so the control source for those text boxes would be the names of those months. This is bad news for when you move to another month. So, instead, you would want to build your query to return, instead of the actual months, fields representing the different months: "M1", "M2", "M3"..."M12". Then, you can always use the same report. You would then have some other calculated fields on that report to translate "M1" into "Nov-13". The same data is presented, but your reports are now dynamic, and can adjust to whatever dates you through at them.

This may be along the lines of how you want to begin.

Bottom line: There are probably better way to present data than through a cross tab query that just keeps expanding to the right. Although, I admit, there may be times when it is necessary.

[Davit]

Twinnyfo, thank you for your suggestion.
I considered that option, but it doesn't meet all my requirements. I was hoping to get a help for this particular situation.
One way to solve it that I have in mind is to check the report width and compare it to the paper width, but I thought there can be an easier way. Hard to believe that Microsoft haven't thought about it already.

[twinnyfo]

Well, keeping with your requirements, the only thing I can think of would be to change the font size for the report. This can make it difficult for others to read (depending on the size of font).

How are you getting the data into the report? Did you just embed the query into the report? I have one report that uses a cross tab query like that. As you described, I had to do it that way out of requirement.

---------------

Here is to thinking outside the box: Have you thought about exporting the query to Excel???? Then, when you print on Excel, it manages all the pagination for you......

It just might work!

[Davit]

Yes, I added the an option to see in excel, and I am just sending the query into excel. However this is only one report in the database. All others have a preview option too, so for the consistency I am keeping it.
Regarding the data entry.
I have a blank template and I am looping through the query columns and adding the boxes into the report. Then I am adding the query to the recordsourse of the report, just to make it easier to calculate the totals for groups.

When user clicks the preview button on the form, this procedure is being called and then the report is being displayed.

 
Select Case FrReports
Case 1
    sRptName = "rProd_Adj_Month_Tab"
    sgRptTitle = "Adjustments by Product, Code and Month"
 
    DoCmd.SetWarnings False
    DoCmd.Close acReport, sRptName, acSaveNo
    On Error Resume Next
    DoCmd.DeleteObject acReport, sRptName
    On Error GoTo 0
    DoCmd.CopyObject , sRptName, acReport, "rCrossTab"
    DoCmd.SetWarnings True
 
    strFilt1 = "qCrossTab_from_WorkData"
    Call OpenCrossTabReport(strFilt1, Application.CurrentProject.AllReports(sRptName), strFilter, OpenArgs)
    DoCmd.Close acReport, sRptName, acSaveYes
 
.
.
.
End Select
 
    Application.Echo True
    DoCmd.OpenReport sRptName, acViewPreview, strFilt1, strFilter, acWindowNormal, OpenArgs
 
 

And this is the function to create the report

Sub OpenCrossTabReport(strSQL As String, obj As AccessObject, strFilter As String, OpenArgs)
 
Dim txbOne As Access.TextBox, txbSum As Access.TextBox, txbTot As Access.TextBox, lblCol As Access.Label, rpt As Report
Dim db As Database, rs As Recordset, offset_pos As Long
 
DoCmd.OpenReport obj.Name, acViewDesign, , strFilter, acWindowNormal, OpenArgs
 
Set rpt = Reports(obj.Name)
 
rpt.lHeader1.Caption = sgCurrentMonth
rpt.lHeader2.Caption = sgRptTitle
 
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)  
 
offset_pos = 0
For i = 5 To rs.Fields.Count - 1
 
'twip: Unit of measurement that is equal to 1/20 of a point, or 1/1,440 of an inch. There are 567 twips in a centimeter.
 
Set txbOne = CreateReportControl(rpt.Name, acTextBox, acGroupLevel2Header, , rs.Fields(i).Name, 4500 + offset_pos, 75)
With txbOne
    .BorderStyle = 0 '"Transparent"
    .Format = "Standard"
    .ControlSource = "=SUM([" & rs.Fields(i).Name & "])"
    .SizeToFit
End With
 
Set txbSum = CreateReportControl(rpt.Name, acTextBox, acGroupLevel1Footer, , "Sum" & rs.Fields(i).Name, 4500 + offset_pos, 0)
With txbSum
    .BackStyle = 0
    .BorderStyle = 0 '"Transparent"
    .Format = "Standard"
    .ControlSource = "=SUM([" & rs.Fields(i).Name & "])"
    .SizeToFit
    .FontWeight = rpt.Controls("lgroupSum").FontWeight
    .ForeColor = rpt.Controls("lgroupSum").ForeColor
End With
 
Set txbTot = CreateReportControl(rpt.Name, acTextBox, acFooter, , "Sum" & rs.Fields(i).Name, 4500 + offset_pos, 10)
With txbTot
    .BackStyle = 0
    .BorderStyle = 0  '0-Transparent
    .GridlineWidthTop = 1
    .GridlineStyleTop = 1
    '.GridlineStyleLeft = 0
    '.GridlineStyleBottom = 0
    '.GridlineStyleRight = 0
    .Format = "Standard"
    .SizeToFit
    .ControlSource = "=SUM([" & rs.Fields(i).Name & "])"
    .FontWeight = rpt.Controls("lgrandTotal").FontWeight
    .ForeColor = rpt.Controls("lgrandTotal").ForeColor
End With
 
Set lblCol = CreateReportControl(rpt.Name, acLabel, acPageHeader, , rs.Fields(i).Name, 4500 + offset_pos, 10)
With lblCol
    .TextAlign = 3  '1-General 2-Center, 3-Right, 4-Distribute
    .SizeToFit
    .Width = txbOne.Width
    .FontItalic = False
    .FontWeight = 600 'semi-bold
    .ForeColor = rpt.Controls("lprod").ForeColor
End With
 
offset_pos = offset_pos + txbOne.Width + 100
 
Next i
 
rpt.RecordSource = strSQL
 
'DoCmd.OpenReport rpt.Name, acViewPreview, , strFilter, acWindowNormal, OpenArgs
 
rs.Close
Set rs = Nothing
Set rpt = Nothing
Set db = Nothing
 
End Sub
 

Sorry for bad "handwriting" :-)

I did some macros in excel before, but this is my second access macro and the first that works with reports. I would appreciate your opinion. Thank you for your help and time.

[twinnyfo]

Davit,

Sorry for taking a while to get back to you on this. Your code actually looks quite nifty!

BUT!!!!

I just thought of something else.....

Since you are building your report and keeping track of where on the report your controls go.... Keep track of how many pages "wide" your report will be (perhaps use a variable called intPages, which will start at 1). Each time you reach the right margin of your page and have to add another control, increment intPages by 1. Then, in your page footer, if you have a text box to calculate pages, use the following:

Me.txtPages.ControlSolurce = "Page " & [Page] & " of " & [Pages] * intPages

Be sure to make this an unbound control, first.

Hope this hepps!

[Davit]

Thank you very much, twinnyfo. Your words are really encouraging, as I don't feel in Access as comfortable as I feel in Excel :-)