Report has 126 pages and take forever to run
in [Reports]
|
Prev: Query results not showing nulls
Next: missing data
From: gmazza via AccessMonster.com on 2 Nov 2009 12:35 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
From: Marshall Barton on 3 Nov 2009 11:52
gmazza via AccessMonster.com wrote: >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. -- Marsh MVP [MS Access] |