Report Freeze Due to On_Format Event

[Radhika Malik]

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.

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.

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]

Answered in your post 50 minutes later.