RE: Dynamate report creation....help

"up to max. 31"... are these for days of the month? Is there a reason why you
are using a solution like this? There are possibly more efficient crosstab
reports.

Can you describe your crosstab query and data?

--
Duane Hookom
Microsoft Access MVP


"Danka" wrote:

I have the following code to generate an Access report based on an Acsess
query. It depends on the number of columns (up to max. 31 that is the max
number of column headers I can assign to the letter size paper in landscape
mode) in the query and it will construct the report for it. For wharever
reason the report takes very long to generate and tahe forever to send to
the printer...last time I waited for over 1.5 hour befoer I killed the
proceess. Please can anyone point me out what is wrong with my coding. It
seems to me the "Detail1_Format" is the source of the delay/problem. For a
small recordset (20 rows and 25 columns) I managed to open the report and
send it to the printer in about 5 minutes.....however it took very long last
time with 26 columns and 200 rows in the recordset before I killed the print
job. Thanks.

Option Compare Database
Option Explicit

'Constant for maximum number of columns Outcome Measure query would create
plus 1 for a Totals column.
Const TOTCOLS = 31

'Variables for database object and recordset.
Dim RptDB As Database
Dim RptRS As Recordset

' Variables for number of columns and row and report totals.
Dim IColCnt As Integer
Dim RgColTot(1 To TOTCOLS) As Long
Dim RptTotal As Long

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
'Place values in text boxes and hide unused text boxes.
Dim i As Integer
Dim strField As String

'Verify that not at end of recordset.
If Not RptRS.EOF Then
'If FormatCount is 1, place values from recordsSet into text boxes
in detail section.
If Me.FormatCount = 1 Then
For i = 1 To IColCnt
'Convert null values to 0.
'Me("Col" + Format$(i)) = xtabCnulls(RptRS(i - 1))
Me("Col" + Format$(i)) = Nz(RptRS(i - 1), 0)
If i >= 3 Then
strField = "F" & i - 2
Me("Tail" + Format$(i)) = DCount(strField, "Scantron",
"Status= 'Valid'")
End If
Next i
'Hide unused text boxes in detail section.
For i = IColCnt + 2 To TOTCOLS
Me("Col" + Format$(i)).Visible = False
Next i
' Move to next record in recordset.
RptRS.MoveNext
End If
End If

End Sub


Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)
Const WHITE = 16777215
Const GREY = 13092807

If (Me![LineNum] Mod 2) = 0 Then
Me![BackGround].BackColor = GREY
Else
Me![BackGround].BackColor = WHITE
End If

End Sub

Private Sub Detail1_Retreat()

' Always back up to previous record when detail section retreats.
RptRS.MovePrevious

End Sub

Private Sub InitVars()

Dim i As Integer

' Initialize RptTotal variable.
RptTotal = 0

' Initialize array that stores column totals.
For i = 1 To TOTCOLS
RgColTot(i) = 0
Next i

End Sub


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

Dim i As Integer

' Put column headings into text boxes in page header.
For i = 1 To IColCnt
Me("Head" + Format$(i)) = RptRS(i - 1).Name
Next i

' Hide unused text boxes in page header.
For i = (IColCnt + 2) To TOTCOLS
Me("Head" + Format$(i)).Visible = False
Next i
End Sub

Private Sub Report_Open(Cancel As Integer)
'DoCmd.Maximize
' Create underlying recordset for report using criteria entered in
' Outcome Measure form.
'
Dim i As Integer
Dim MyQuery As querydef


' Set database variable to current database.
Set RptDB = DBEngine.Workspaces(0).Databases(0)

' Open QueryDef.
Set MyQuery = RptDB.QueryDefs("qryValidCandidateTotalVote")

' Open Recordset.
Set RptRS = MyQuery.OpenRecordset()

' If no records match criteria, display message,
' close recordset, and cancel Open event.
If RptRS.RecordCount = 0 Then
MsgBox "No records match the criteria you entered.", 48, "No Records
Found"
RptRS.Close
Cancel = True
Exit Sub
End If

' Set a variable to hold number of columns in crosstab query.
IColCnt = RptRS.Fields.Count

End Sub

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

' Move to first record in recordset at beginning of report
' or when report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
RptRS.MoveFirst

'Initialize variables.
InitVars

End Sub



.

Relevant Pages

  • Re: access 2003
    ... Dim ctl As Control ... Dim rs As Recordset ... This sets the query definitions for choosing data to create an invoice using ... Event on combo box: Private Sub ChooseCust_AfterUpdate ...
    (microsoft.public.access.conversion)
  • Re: Printed report does not match print Preview
    ... I am unfamiliar with how the report process works. ... > Dim rstReport As DAO.Recordset ... > Dim intX As Integer ... > Private Sub Detail_Format ...
    (microsoft.public.access.reports)
  • Re: Error: Microsoft jet database engine does not recognize " as a va
    ... If you are attempting to create a crosstab report with dynamic columns, ... > Dim rstReport As DAO.Recordset ... > Dim intX As Integer ... > Private Sub Detail_Format ...
    (microsoft.public.access.reports)
  • Re: Printed report does not match print Preview
    ... I am unfamiliar with how the report process works. ... Dim rstReport As DAO.Recordset ... Dim intX As Integer ... Private Sub Detail_Format ...
    (microsoft.public.access.reports)
  • Re: Error: Microsoft jet database engine does not recognize " as
    ... Report Propoerties window or would it need to be done through the VB code? ... >> ' Variables for Database object and Recordset. ... >> Dim rstReport As DAO.Recordset ... >> Private Sub Detail_Format ...
    (microsoft.public.access.reports)