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.

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.)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"radhikam" <radhikam@discussions.microsoft.com> wrote in message 
news:D4961EB1-5621-4E5D-B215-0F45C368FE02@microsoft.com...
> 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.