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.
