On Format Print Preview Freeze

[radhikam]

I'm using Access 2007 and am currently in the process of designing a report
that has many controls (>20) and sub-reports (8). Each time I try to open the
report in Print Preview mode Access 2007 freezes (whether it's by switching
to print preview from design view or by right clicking on the report name and
trying to print preview it
or by doing print preview from report view). My computer is more than 3
years old, and I'm wondering if it's only an issue due to my computer's slow
performance. If I remove the on-format event the report works fine.

I have the same code in the event that I have in the on-current event of
another form, and that works absolutely fine. Currently I have 13 records
that the report has to print preview of, but when the DB is deployed more
than 100 records will have to be print-previewed at the same time. When I put
the same code in the on_current event
of the report, it works fine if I select a particular record in layout
view/report view as it should.

Here's my code-

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Err_Detail_Format
Me!Month1 = MonthName(Month(Me!StartDate))
If Type.Value = "New" Then
TypeCheck.Value = 1
Type.Visible = False
ElseIf Type.Value = "Relapse" Then
TypeCheck.Value = 2
Type.Visible = False
ElseIf Type.Value = "Transfer" Then
TypeCheck.Value = 3
Type.Visible = False
ElseIf Type.Value = "Failure" Then
TypeCheck.Value = 4
Type.Visible = False
ElseIf Type.Value = "default" Then
TypeCheck.Value = 5
Type.Visible = False
ElseIf Type.Value Like "Other:*" Then
TypeCheck.Value = 6
Type.Visible = True
Else
TypeCheck.Value = Null
Type.Visible = False
End If
If DC.Value = "P" Then
DCCheck.Value = 1
ElseIf DC.Value = "EP" Then
DCCheck.Value = 2
Else
DCCheck.Value = Null
End If
If TType.Value = "PSPositive" Then
TTypeCheck.Value = 1
ElseIf TType.Value = "SiSN" Then
TTypeCheck.Value = 2
ElseIf TType.Value = "SiEP" Then
TTypeCheck.Value = 3
ElseIf TType.Value = "relapses" Then
TTypeCheck.Value = 4
ElseIf TType.Value = "failure" Then
TTypeCheck.Value = 5
ElseIf TType.Value = "default" Then
TTypeCheck.Value = 6
ElseIf TType.Value = "others" Then
TTypeCheck.Value = 7
ElseIf TType.Value = "PSNP" Then
TTCheck.Value = 8
ElseIf TType.Value = "EPNSI" Then
TTypeCheck.Value = 9
Else
TTypeCheck.Value = Null
End If
If CType.Value = "I" Then
CTypeCheck.Value = 1
ElseIf CType.Value = "II" Then
CTypeCheck.Value = 2
ElseIf CType.Value = "III" Then
CTypeCheck.Value = 3
ElseIf CType.Value = "Non-DOTS" Then
CTypeCheck.Value = 4
Else
CTypeCheck.Value = Null
End If
If OS.Value = -1 Then
OSCheck.Value = 1
ElseIf OS.Value = 0 Then
OSCheck.Value = 2
Else
OSCheck.Value = Null
End If
If Gender.Value = "M" Then
GenderCheck.Value = 1
ElseIf Gender.Value = "F" Then
GenderCheck.Value = 2
Else
GenderCheck.Value = Null
End If

Dim db1 As Database
Dim Doses As DAO.Recordset
Dim DoseDate As Date
Dim mySQL1 As String
mySQL1 = "SELECT eDate FROM D_Admins WHERE T_ID= " & Me!T_ID & "
AND (S_ID=1 OR S_ID=2) AND Type='Supervised' ORDERBY Date;"
Set db1 = CurrentDb()
Set Doses = db1.OpenRecordset(mySQL1)
Doses.Close
db1.Close
Exit_Detail_Format:
Set Doses = Nothing
Set db1 = Nothing
Err_Detail_Format:
Resume Exit_Detail_Format
End Sub


I was just testing the recordset opening, and had to add more code dealing
with the recordset, but this itself freezes print preview of the report. If I
open the report in report view, then the on_format event doesn't
fire at all, so I have no way to test my code now, and also do not know how
I'll print the report once I have finished coding and designing.

Now even if I remove the part containing the database and recordset, the
report freezes. All subreqports work fine individually, the code compiles,
and other reports (~10) and forms (~50) work fine.

I have tried compact and repair, and decompile but nothing works.

Anyone having any insight on this problem, please do let me know. Any help
will be sincerely appreciated.

 

[Allen Browne]

Suggestions:

1. Temporarily clear any settings you have for KeepTogether etc. on the
report's sections. This will help detemine whether the report is constantly
trying to fit things onto the page that won't fit, and so advancing and
retreating repeatedly.

2. Around all the code in the procedure (except the error handler), add:
If FormatCount = 1 Then
End If
This will avoid the code running multiple times.

3. You're opening a recordset, but not using it. This is the slowest part of
the code. Remove it.

4. Where possible, use other techniques rather than code. For example,
instead of:
Me!Month1 = MonthName(Month(Me!StartDate))
just use a text box with these properties:
Control Source StartDate
Format mmmm

5. Be careful of reserved words like Type. Perhaps you could use Me![Type]
instead for this case.

6. Instead of having to code these values into your report, it would be more
efficient to create some lookup tables that contain the names and matching
numbers. You can then include these lookup tables in your report (using
outer joins in the source query), and you can then read the values from the
fields in the table instead of having to code the If ... ElseIf ... blocks.
(Not only will this be much faster, but it will also work in those views
where the code doesn't run, e.g. Report view and Layout view.)