On Format Event Freezing Report (1 Viewer)

[radhika1990]

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.

 

[RuralGuy]

Do you think it could have anything to do with the fact that Type is a reserved word?

 

[radhika1990]

Thanks a lot for your reply.

I changed the name, but still isn't helping. I thought the error was with "ORDERBY" but fixing it to "ORDER BY" didn't change anything either.

I'm entirely lost :/

 

[RuralGuy]

The mySQL1 string does not look right and why are you opening and closing that Recordset anyway?

 

[gemma-the-husky]

try setting a breakpoint and stepping through

you might have some error thats getting the code stuck in a never ending loop


in fact - you ARE getting stuck right at the bottom, surely

Exit_Detail_Format:
Set Doses = Nothing
Set db1 = Nothing
exit sub ' REQUIRED
Err_Detail_Format:
Resume Exit_Detail_Format

 

[gemma-the-husky]

try setting a breakpoint and stepping through

you might have some error thats getting the code stuck in a never ending loop


in fact - you ARE getting stuck right at the bottom, surely - although I am not sure whether resume without an active error ought to generate an rte

Exit_Detail_Format:
Set Doses = Nothing
Set db1 = Nothing
exit sub ' REQUIRED
Err_Detail_Format:
Resume Exit_Detail_Format

 

[RuralGuy]

Good catch Dave; especially on that hard to read unformatted code.