Report has 126 pages and take forever to run

  • Thread starter gmazza via AccessMonster.com
  • Start date
G

gmazza via AccessMonster.com

Hey there,
I'm minorly new to reports and simple ones are working fine but I have this
complex one that has 399 text boxes on it and in the On Open event I open up
2 different recordsets to see which textboxes get displayed, along with their
relevant data, and which disappear.
All the logic works except 2 problems:
1. My report has 126 pages
2. It takes a good minute to run, which is way too long.

For the 126 pages, my data in the On Open event is only showing 100 textboxes,
and since they are layed out 8 per line, it doesn' take up much room, maybe
3/4 of a normal page. Every odd page is the exact same as the 1st page which
is all I want, the first page. Every even page just has the page headers. Can
anyone explain?

Why it takes so long to run is beyond me too. Is it my code? My database is
compacted and really small and I don't have much data. Any suggestions?
Thanks for all your help in advance!
 
G

gmazza via AccessMonster.com

Thanks for the reply Gina. My name is Gino :)
The record source of my report is based on a table, not a query so its tough
to say. If I open the table it opens no problem.
In the page footer I have a text box with this as a control source:
="Page " & [Page] & " of " & [Pages]

I deleted it but it was still 126 pages and took long to open.

Gina said:
gmazza,

Does the RecordSource of the report open quickly? If yes, do you have page
numbers on your report? If Yes, then remove the Page numbering and see what
happens.
Hey there,
I'm minorly new to reports and simple ones are working fine but I have
[quoted text clipped - 22 lines]
compacted and really small and I don't have much data. Any suggestions?
Thanks for all your help in advance!
 
M

Marshall Barton

gmazza said:
I'm minorly new to reports and simple ones are working fine but I have this
complex one that has 399 text boxes on it and in the On Open event I open up
2 different recordsets to see which textboxes get displayed, along with their
relevant data, and which disappear.
All the logic works except 2 problems:
1. My report has 126 pages
2. It takes a good minute to run, which is way too long.

For the 126 pages, my data in the On Open event is only showing 100 textboxes,
and since they are layed out 8 per line, it doesn' take up much room, maybe
3/4 of a normal page. Every odd page is the exact same as the 1st page which
is all I want, the first page. Every even page just has the page headers. Can
anyone explain?

Why it takes so long to run is beyond me too. Is it my code? My database is
compacted and really small and I don't have much data. Any suggestions?


When you get the same information on every page, it's
usually because the recport's record source query is
returning too many records. To figure out what's going on,
you should work with the query by itself, the report is
probably just getting in the way of analyzing the problem.

The query being overly complex might(?) also be the reason
for the long time to see the report. Again, test the query
by itself. If the query runs a lot faster than the report,
then it might be the reports grouping or ??

As for the even pages only having the page header, that is
often caused by having the ForceNewPage property set
inappropriately. Another thing to check that can cause this
is when the report's width exceeds the space between the
margins.
 
G

Gina Whipp

Gino :eek:

Well then I am going to have to say that is must be the Report set-up. You
say 100 fields per page and the Report Header every other page, why? It
could be this paging that is causing the slowness. Are they the same 100
fields or does the data *rotate* depending on which page?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

gmazza via AccessMonster.com said:
Thanks for the reply Gina. My name is Gino :)
The record source of my report is based on a table, not a query so its
tough
to say. If I open the table it opens no problem.
In the page footer I have a text box with this as a control source:
="Page " & [Page] & " of " & [Pages]

I deleted it but it was still 126 pages and took long to open.

Gina said:
gmazza,

Does the RecordSource of the report open quickly? If yes, do you have
page
numbers on your report? If Yes, then remove the Page numbering and see
what
happens.
Hey there,
I'm minorly new to reports and simple ones are working fine but I have
[quoted text clipped - 22 lines]
compacted and really small and I don't have much data. Any suggestions?
Thanks for all your help in advance!
 
G

gmazza via AccessMonster.com

Thanks for the reply Marshall.
I based my report on a query as you suggested and it fixed the page problem,
down to 2 pages which is right.
The query runs in 1 second, but the report still takes 25 seconds.
Like I said, it must be because of the code in my On Open event as I am doing
a lot in there. There are no grouping or sorting on this report at all.
Here is the code in my On Open and see maybe if its anything in there.
Private Sub Report_Open(Cancel As Integer)
Dim fld As DAO.Field
Dim intCount As Integer
Dim rs As DAO.Recordset
Dim study As String
Dim textCount As Integer
Dim TitleFlag As Boolean
Dim LabelCount As Integer

study = GetActiveStudy()

intCount = 0
textCount = 0

Set rs = CurrentDb.OpenRecordset("Select * from AssessCriteria where
ClinicalTrialId = '" & study & "'")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF

For Each fld In rs.Fields

If fld.Name = "Title" Then
If fld.Value = True Then
intCount = intCount + 1
textCount = textCount + 7
TitleFlag = True

With Me.Controls("Text" & textCount)
.Visible = False
End With

End If
End If

If fld.Name = "CriteriaValue" Then
If IsNull(fld.Value) Then
Else
If TitleFlag = True Then

With Me.Controls("Label" & intCount)
.Caption = fld.Value
.Visible = True
.FontBold = True
End With

Else
intCount = intCount + 1
textCount = textCount + 7
With Me.Controls("Label" & intCount)
.Caption = fld.Value
.Visible = True
End With
End If
TitleFlag = False
End If
End If

Next fld
rs.MoveNext
Loop

End If

' Clean up objects.
Set fld = Nothing
rs.Close
Set rs = Nothing

LabelCount = intCount

' Hide unused labels
While intCount < 57
intCount = intCount + 1
Me.Controls("Label" & intCount).Visible = False
Wend

'-----------------------------------------------------------------------------
-------------
Dim Day As String
Dim ValueCount As Integer
Dim DayCount As Integer
Dim TotalCount As Integer
Dim Week As Integer
Dim ConSource As String

Week = 1

Set rs = CurrentDb.OpenRecordset("Select * from Assessment where
ClinicalTrialId = '" & study & "'" & _
" and WeekId = '" & Week & "' and PatientId = '" & GetActivePatient() & "'")

If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF

For Each fld In rs.Fields
TotalCount = 0

If fld.Name = "DayId" Then
If IsNull(fld.Value) Then
Else
Day = fld.Value

DayCount = DayCount + 1
ValueCount = DayCount

While TotalCount < 57
TotalCount = TotalCount + 1


ConSource = Nz(DLookup("Value" & TotalCount, "Assessment",
"WeekId = '" & [Forms]![patient]![txtWeek] & _
"' and PatientId = '" & GetActivePatient() & "' and DayId =
'" & Day & "' and ClinicalTrialId = '" & GetActiveStudy() & "'"))

Me.Controls("text" & ValueCount).ControlSource = "= '" &
ConSource & "'"

ValueCount = ValueCount + 7

Wend

End If
End If

Next fld
rs.MoveNext
Loop

End If

Set fld = Nothing
rs.Close
Set rs = Nothing

LabelCount = LabelCount * 7

While LabelCount < 399
LabelCount = LabelCount + 1
Me.Controls("Text" & LabelCount).Visible = False
Wend

End Sub


Marshall said:
I'm minorly new to reports and simple ones are working fine but I have this
complex one that has 399 text boxes on it and in the On Open event I open up
[quoted text clipped - 12 lines]
Why it takes so long to run is beyond me too. Is it my code? My database is
compacted and really small and I don't have much data. Any suggestions?

When you get the same information on every page, it's
usually because the recport's record source query is
returning too many records. To figure out what's going on,
you should work with the query by itself, the report is
probably just getting in the way of analyzing the problem.

The query being overly complex might(?) also be the reason
for the long time to see the report. Again, test the query
by itself. If the query runs a lot faster than the report,
then it might be the reports grouping or ??

As for the even pages only having the page header, that is
often caused by having the ForceNewPage property set
inappropriately. Another thing to check that can cause this
is when the report's width exceeds the space between the
margins.
 
M

Marshall Barton

gmazza said:
I based my report on a query as you suggested and it fixed the page problem,
down to 2 pages which is right.
The query runs in 1 second, but the report still takes 25 seconds.
Like I said, it must be because of the code in my On Open event as I am doing
a lot in there. There are no grouping or sorting on this report at all.
Here is the code in my On Open and see maybe if its anything in there.
Private Sub Report_Open(Cancel As Integer)
Dim fld As DAO.Field
Dim intCount As Integer
Dim rs As DAO.Recordset
Dim study As String
Dim textCount As Integer
Dim TitleFlag As Boolean
Dim LabelCount As Integer

study = GetActiveStudy()

intCount = 0
textCount = 0

Set rs = CurrentDb.OpenRecordset("Select * from AssessCriteria where
ClinicalTrialId = '" & study & "'")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF

For Each fld In rs.Fields

If fld.Name = "Title" Then
If fld.Value = True Then
intCount = intCount + 1
textCount = textCount + 7
TitleFlag = True

With Me.Controls("Text" & textCount)
.Visible = False
End With

End If
End If

If fld.Name = "CriteriaValue" Then
If IsNull(fld.Value) Then
Else
If TitleFlag = True Then

With Me.Controls("Label" & intCount)
.Caption = fld.Value
.Visible = True
.FontBold = True
End With

Else
intCount = intCount + 1
textCount = textCount + 7
With Me.Controls("Label" & intCount)
.Caption = fld.Value
.Visible = True
End With
End If
TitleFlag = False
End If
End If

Next fld
rs.MoveNext
Loop

End If

' Clean up objects.
Set fld = Nothing
rs.Close
Set rs = Nothing

LabelCount = intCount

' Hide unused labels
While intCount < 57
intCount = intCount + 1
Me.Controls("Label" & intCount).Visible = False
Wend

'-----------------------------------------------------------------------------
-------------
Dim Day As String
Dim ValueCount As Integer
Dim DayCount As Integer
Dim TotalCount As Integer
Dim Week As Integer
Dim ConSource As String

Week = 1

Set rs = CurrentDb.OpenRecordset("Select * from Assessment where
ClinicalTrialId = '" & study & "'" & _
" and WeekId = '" & Week & "' and PatientId = '" & GetActivePatient() & "'")

If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF

For Each fld In rs.Fields
TotalCount = 0

If fld.Name = "DayId" Then
If IsNull(fld.Value) Then
Else
Day = fld.Value

DayCount = DayCount + 1
ValueCount = DayCount

While TotalCount < 57
TotalCount = TotalCount + 1


ConSource = Nz(DLookup("Value" & TotalCount, "Assessment",
"WeekId = '" & [Forms]![patient]![txtWeek] & _
"' and PatientId = '" & GetActivePatient() & "' and DayId =
'" & Day & "' and ClinicalTrialId = '" & GetActiveStudy() & "'"))

Me.Controls("text" & ValueCount).ControlSource = "= '" &
ConSource & "'"

ValueCount = ValueCount + 7

Wend

End If
End If

Next fld
rs.MoveNext
Loop

End If

Set fld = Nothing
rs.Close
Set rs = Nothing

LabelCount = LabelCount * 7

While LabelCount < 399
LabelCount = LabelCount + 1
Me.Controls("Text" & LabelCount).Visible = False
Wend

End Sub


That's a lot of code and processing a bunch of controls for
every field in every record for two recordsets can be huge
if there are more than a very few records in the recordsets.
Having a DLookup in one of those loops may be the killer
because, behind the scenes, it is yet another
query/recordset.

Without spending a lot of time analyzing your entire
situation, I really do not want to speculate on what can be
done to speed it up.

If you really need to do all that processing, I suggest that
you live with the half minute delay.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top