Long reports

[JustJim]

Against my better judgement, I'm making what is going to be a large report based on the analysis of some survey returns.

I've just re-read Adezii's wonderful article on the specifications in Access and it says that I can have 65,000 odd pages (not going to, but I can) but a section can only be 22 in. (55.87 cm) long, which seem to require a three page breaks per thousandth of an inch or something.

How do I get a listbox, which has no "Can Grow" property to use as many pages as required?

These listboxes are populated by code referenced in their Row Source Type and this type of Sub does have a named argument called acLBGetRowCount. Do I multiply the row count by a size factor for the font to set the height of the listbox control?

Muddled in Melbourne
Jim

[JustJim]

Against my better judgement, I'm making what is going to be a large report based on the analysis of some survey returns.

I've just re-read Adezii's wonderful article on the specifications in Access and it says that I can have 65,000 odd pages (not going to, but I can) but a section can only be 22 in. (55.87 cm) long, which seem to require a three page breaks per thousandth of an inch or something.

How do I get a listbox, which has no "Can Grow" property to use as many pages as required?

These listboxes are populated by code referenced in their Row Source Type and this type of Sub does have a named argument called acLBGetRowCount. Do I multiply the row count by a size factor for the font to set the height of the listbox control?

Muddled in Melbourne
Jim

Maybe I'll just export the whole darn thing to Excel!

[JustJim]

Maybe I'll just export the whole darn thing to Excel!

Bump

Not urgent, not stressing, just wondering if anyone has any thoughts.

Jim

[JustJim]

Bump

Not urgent, not stressing, just wondering if anyone has any thoughts.

Jim

Bump again

Not really urgent, not stressing much, the medication seems to be working.

This is a different report, and has to have eight fixed size listboxes but there is only room for one per A4 (letter size) page and I just run out of room. How can I have 65,000 odd pages if I can only have 20" of detail?
Jim

[JustJim]

Bump again

Not really urgent, not stressing much, the medication seems to be working.

This is a different report, and has to have eight fixed size listboxes but there is only room for one per A4 (letter size) page and I just run out of room. How can I have 65,000 odd pages if I can only have 20" of detail?
Jim

I seem to have gotten away with adding a couple of group headers using bogus fields. Not very elegant, there's got to be a better way.

Jim

[missinglinq]

Don't mean to butt into this conversation you're having with yourself, Jim, but here's a few things for you!

The 22" is the working length of of the Detail section or "page" on the screen, and to be honest, probably shouldn't ever be maxed out! Tabbed pages should be used if you need that much real estate for a form. At any rate, this has nothing to do with the printed page. The 65,000 pages is the spec for the actual number of pages that can be printed out. You might have page that only consisted of a customer's name address and phone number, but have have 65,000 customers; the length of the detail section and the number of pages in a report have nothing to do with one another.

The reason that a listbox doesn't have a Can Grow property is that a listbox is a data entry device! Access basically uses the same interface for reports and forms, which is why the listbox (as well as the combobox) is up in the toolbar, but it's not meant to be part of a printed report!

You should , instead, use a subreport. You can use the same SQL/Query as a recordsource, and subreports do have the Can Grow Property!

Linq ;0)>

[JustJim]

Don't mean to butt into this conversation you're having with yourself, Jim, but here's a few things for you!

The 22" is the working length of of the Detail section or "page" on the screen, and to be honest, probably shouldn't ever be maxed out! Tabbed pages should be used if you need that much real estate for a form. At any rate, this has nothing to do with the printed page. The 65,000 pages is the spec for the actual number of pages that can be printed out. You might have page that only consisted of a customer's name address and phone number, but have have 65,000 customers; the length of the detail section and the number of pages in a report have nothing to do with one another.

The reason that a listbox doesn't have a Can Grow property is that a listbox is a data entry device! Access basically uses the same interface for reports and forms, which is why the listbox (as well as the combobox) is up in the toolbar, but it's not meant to be part of a printed report!

You should , instead, use a subreport. You can use the same SQL/Query as a recordsource, and subreports do have the Can Grow Property!

Linq ;0)>

Did you hear that voice?
Yes I did.
Spooky wasn't it?
It was, but it made a certain amount of sense.
It did, but sadly, we fill our listboxes by code so that judicious usage of Variable names etc makes them very "swappable". Also once the list box is filled, it is available for writing to Excel since the same Cartesian (x,y) style is used.
Yes we do, but maybe we've shot ourselfs in the footses by trying to be too smart, my precious.
Perhaps, but shhh, here comes the nurse with the medication.

Jim (and special friend)

[sierra7]

Jim

I haven't a clue what you are talking about putting a List Box in a report but must try it sometime ! The nearest I've been is when I tried the PrintOut command to print a screen rather than write a proper report.

MissingLinq's idea of putting stuff in sub-reports makes a whole lot more sense. If your data was suitable you could maybe set column widths to put multiple columns on a page if number of sheets of paper is an issue. (Also make the font smaller; whose going to read 65,000 pages anyway?)

However, I do have experience of writing to Excel where there is a 65,000 row limit (65,536 actually and ROWS not PAGES. Customer wanted to de-normalise a perfectly respectable relational database resulting in millions of rows, to export to Excel for sucking in to an Oracle system) You can work out how many Excel files resulted and maybe guess at how easy they were to manage (not).

Stick to Access, don't go to Excel !

[JustJim]

Jim

I haven't a clue what you are talking about putting a List Box in a report but must try it sometime ! The nearest I've been is when I tried the PrintOut command to print a screen rather than write a proper report.

MissingLinq's idea of putting stuff in sub-reports makes a whole lot more sense. If your data was suitable you could maybe set column widths to put multiple columns on a page if number of sheets of paper is an issue. (Also make the font smaller; whose going to read 65,000 pages anyway?)

However, I do have experience of writing to Excel where there is a 65,000 row limit (65,536 actually and ROWS not PAGES. Customer wanted to de-normalise a perfectly respectable relational database resulting in millions of rows, to export to Excel for sucking in to an Oracle system) You can work out how many Excel files resulted and maybe guess at how easy they were to manage (not).

Stick to Access, don't go to Excel !

Well, I suppose I should explain, in case anyone else is ever interested.

Well I never wanted 65K pages, I have more respect for trees than that! The way it works now is that when I can control the number of rows in the output (top 25 schools, Languages spoken by year level etc) I format a list box on a report (example code next post if anyone's interested) and display the print preview of the report, letting the operator decide to print or not. When I can't control the output so well (all courses undertaken by all teachers in the state), I make the same sort of list box on a report but open the report hidden, then simply run across the rows and down the columns with two nested for...next loops and send each location to a cell on a spreadsheet. (code for this in next post).

The sub for filling a list box is familiar to me (now, it took a while) and I use it a lot.

Jim

[JustJim]

Example code to fill a list box

Function FillMEA(fld As Control, ID As Variant, row As Variant, col As Variant, code As Variant) As Variant
Static SurvDB As Database
Static rsData As Recordset
Static rsCourses As Recordset
Static strField As String, strStaff As String, Region As String, strSQL As String
Static cntCourse As Long, cntData As Long, Counter As Long
Static Match As Long
Static ColWidth(2) As Long
Static DisplayData() As Variant
Dim ReturnVal As Variant
ColWidth(0) = -1
ColWidth(1) = 400
ColWidth(2) = 400
Match = 0
Select Case code
    Case acLBInitialize
        Region = Reports!rptTotalS.OpenArgs
        Set SurvDB = CurrentDb()
        strSQL = "SELECT ALL_CAMPUSES.CAMPUS_TYPE, tblReturnData.PriSecReturnAs, tblReturnData.MEA_PD_Desc1, tblReturnData.MEA_PD_Staff1, " _
        & "tblReturnData.MEA_PD_Desc2, tblReturnData.MEA_PD_Staff2, tblReturnData.MEA_PD_Desc3, " _
        & "tblReturnData.MEA_PD_Staff3, tblReturnData.MEA_PD_Desc4, tblReturnData.MEA_PD_Staff4, ALL_SCHOOLS.REGION_ID " _
        & "FROM ALL_SCHOOLS INNER JOIN (ALL_CAMPUSES INNER JOIN tblReturnData " _
        & "ON (ALL_CAMPUSES.SCHOOL_NO = tblReturnData.SchoolNo) AND (ALL_CAMPUSES.CAMPUS_NO = tblReturnData.CampusNo)) " _
        & "ON (ALL_SCHOOLS.SCHOOL_NO = tblReturnData.SchoolNo) AND (ALL_SCHOOLS.SCHOOL_NO = ALL_CAMPUSES.SCHOOL_NO) "
        strSQL = strSQL & Region
        'variable Region is from the report's OpenArg and looks like this
        ' "WHERE (((ALL_SCHOOLS.REGION_ID)=" & Region & "))"
        Set rsData = SurvDB.OpenRecordset(strSQL, dbOpenDynaset)
        Set rsCourses = SurvDB.OpenRecordset("tblCourses", dbOpenDynaset)
        rsCourses.MoveLast
        rsCourses.MoveFirst
        ReDim DisplayData(3, rsCourses.RecordCount)
        'Put an asterisk in (0,0) in case recordset is empty
        DisplayData(0, 0) = "*"
        If rsData.EOF Then GoTo rtn:
        'rs not empty so put title in (0,0)
        DisplayData(0, 0) = "MEA PD"
        rsData.MoveLast
        rsData.MoveFirst
        '   DisplayData(0=Column Head 1=Course Name 2=Pri Numbers 3=Sec Numbers, Number of courses in tblCourses)
        DisplayData(1, 0) = " Pri"
        DisplayData(2, 0) = " Sec"
        strField = "MEA_PD_Desc"
        strStaff = "MEA_PD_Staff"
        For cntCourse = 0 To rsCourses.RecordCount - 1
            rsCourses.AbsolutePosition = cntCourse
            For cntData = 0 To rsData.RecordCount - 1
            rsData.AbsolutePosition = cntData
                For Counter = 1 To 4
                    If rsData.Fields(strField & Format(Counter, "@")) = rsCourses!course Then
                        Select Case rsData!Campus_Type
                            Case "Primary"
                                DisplayData(1, cntCourse + 1) = DisplayData(1, cntCourse + 1) + rsData.Fields(strStaff & Format(Counter, "@"))
                            Case "Secondary"
                                DisplayData(2, cntCourse + 1) = DisplayData(2, cntCourse + 1) + rsData.Fields(strStaff & Format(Counter, "@"))
                            Case "Pri/Sec"
                                Select Case rsData!PriSecReturnAs
                                    Case "Primary"
                                        DisplayData(1, cntCourse + 1) = DisplayData(1, cntCourse + 1) + rsData.Fields(strStaff & Format(Counter, "@"))
                                    Case "Secondary"
                                        DisplayData(2, cntCourse + 1) = DisplayData(2, cntCourse + 1) + rsData.Fields(strStaff & Format(Counter, "@"))
                                End Select ' PriSecReturnAs
                        End Select ' Campus type
                        If Match = 0 Then
                            DisplayData(0, cntCourse + 1) = rsData.Fields(strField & Format(Counter, "@"))
                            Match = Match + 1
                        End If
                    End If
                Next Counter
            Next cntData
            Match = 0
        Next cntCourse
rtn:
        ReturnVal = True
 
    Case acLBOpen
        ReturnVal = Timer
 
    Case acLBGetRowCount
        ReturnVal = rsCourses.RecordCount + 1
 
    Case acLBGetColumnCount
        ReturnVal = 3
 
    Case acLBGetColumnWidth
        ' -1 uses default, other values in twips (567 per cm)
        ReturnVal = ColWidth(col)
 
    Case acLBGetValue
        ReturnVal = DisplayData(col, row)
 
End Select
 
FillMEA = ReturnVal
 
End Function
 
 

[JustJim]

And to export to Excel (note lots of rem'd out lines which can be used for formatting)

Function ExportExcelData(arData() As Variant, fileNameWithPath As String)
Dim objExcel As New Excel.Application
Dim strRange As String
If objExcel Is Nothing Then
    MsgBox "Could not start Excel."
    Screen.MousePointer = vbNormal
    GoTo CleanUpAndLeave
End If
Dim wb As Workbook
Dim ws As Worksheet
Dim iX As Long
Dim iY As Long
'Dim strBottomRight As String
Set wb = objExcel.Workbooks.Add
Set ws = objExcel.Worksheets.Add
 
    For iY = 1 To UBound(arData, 2) + 1
        For iX = 1 To UBound(arData, 1) + 1
            ws.Cells(iY, iX).Value = arData(iX - 1, iY - 1)
            'Debug.Print iX
        Next iX
        'Debug.Print , iY
    Next iY
'ws.Range("A1").Font.Size = 12
'ws.Range("A3").Font.Italic = True
'ws.Range("A4:B4").Font.Bold = True
'strRange = "B7:P" & CStr(iY - 3)
'With ws.Range(strRange).Borders(xlInsideVertical)
'    .LineStyle = xlContinuous
'    .Weight = xlThin
'    .Color = vbBlack
'End With
ws.Columns.HorizontalAlignment = xlLeft
ws.Columns.AutoFit
'ws.Rows(4).Font.Bold = True
'ws.Columns(2).ColumnWidth = 40
ws.Columns(1).ColumnWidth = 30
'ws.Columns(13).ColumnWidth = 4
ws.Columns(1).WrapText = True
'ws.Columns(2).WrapText = True
ws.PageSetup.Orientation = xlLandscape
wb.SaveAs fileNameWithPath
 
CleanUpAndLeave:
objExcel.Quit
Set objExcel = Nothing
Set wb = Nothing
Set ws = Nothing
DoCmd.SetWarnings True
DoCmd.Hourglass False
Exit Function
 
End Function
 

[sierra7]

WOW !
I see why you are rated a Programmer although I was chuffed that I managed to follow most of the code!

I've tended to avoid multi-dimensional arrays since using Access and prefer to append data in a temporary (local) table if I want it for onward processing. (e.g. charting) It has the advantage that when things go wrong you can inspect the values you are working with, whereas when a lot is held in memory (be it arrays or correleted sub-queries) all is lost if the code breaks. There are many pros and cons for each, I know.

I still have not fully got my head around why you want a list box and why the same data could not be shown in a sub-form from which the Print Preview command could be given. (Temporary table behind the sub-form?)

I'm getting out of my depth (and must go and earn a buck of my own!) Best of luck.

[JustJim]

WOW !
I see why you are rated a Programmer although I was chuffed that I managed to follow most of the code!

I've tended to avoid multi-dimensional arrays since using Access and prefer to append data in a temporary (local) table if I want it for onward processing. (e.g. charting) It has the advantage that when things go wrong you can inspect the values you are working with, whereas when a lot is held in memory (be it arrays or correleted sub-queries) all is lost if the code breaks. There are many pros and cons for each, I know.

I still have not fully got my head around why you want a list box and why the same data could not be shown in a sub-form from which the Print Preview command could be given. (Temporary table behind the sub-form?)

I'm getting out of my depth (and must go and earn a buck of my own!) Best of luck.

Thanks for your kind words, although as I said earlier, I may be being too smart for my own good. For the reasons you mentioned above as well as something Adezii has brought to our attention recently.

Anyway, as you say, back to working for the man! Gotta eat.

Jim