Re: Report has 126 pages and take forever to run

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 Barton wrote:
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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200911/1

.

Relevant Pages

  • RE: Multiply qty of filtered records
    ... If you should want to try doing this with a query (which is probably the more ... etc in the bottom part of the design view. ... I do know how to get this information in a Report, ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • RE: vary report label caption using form & table info
    ... a template for the label/envelope....which would be driven off the same query. ... Command button on the form opens the report "template. ... The label caption has 3 placeholders. ... Dim StartToken As Long ...
    (microsoft.public.access.reports)
  • RE: Need help with Code Please!!!
    ... MsgBox "The qryCompany\USFNumber query returned no records?", ... vbCritical, "NOTHING TO REPORT" ... Dim qd As QueryDef 'object ref to query item ... Dim myDataSource As String ...
    (microsoft.public.access.formscoding)
  • Re: Force Blank Lines
    ... I used your sugestion and I am not getting what I am ... I have a report with vb ... Dim lngDuration As Long 'Length of Usage ... Create a query based on: ...
    (microsoft.public.access.reports)
  • RE: export to excel and format excel rows?
    ... Dim rst As DAO.Recordset ... Dim intCount As Integer ... Set rst = frm.RecordsetClone ... I can export the query using transferspreadsheet. ...
    (microsoft.public.access.externaldata)