MS Access 2007

[holly]

Hi I'm using VBA coe in the above application and I click a button that runs
some code in the background to open and produce a report.

My problem is when the report is trying to open as it stipulates formatting
the page....this can take 20mins to run which Iknow is not right,,

Has anyone ever encountered the same problem and know of a fix??

[Hack]

What kind of code is the button running?

[holly]

when the button is clicked it runs

Private Sub Label24_Click() 'rptOutstandingASNonZSA
On Error GoTo Err_label24_Click

    lPolPayAType_str = "Not Like ""Z*"""
    lPolPayState_int = 6
    gReportZSA_bol = False
    CreateReportTable
    DoCmd.OpenReport "rptOutstandingAS_newNonZSA", acViewPreview

Exit_label24_Click:
    Exit Sub

Err_label24_Click:
    MsgBox Err.Description
    Resume Exit_label24_Click
End Sub


Private Function CreateReportTable()
On Error GoTo Err_CreateReportTable

    DoCmd.SetWarnings False
    
    CreateConnection
    
    gSQLCmd_str = "SELECT Count(AS_PolPayData.CountKey) AS [Number of Trans]"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolPayData.EffectiveStartDate"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolPayData.Comments"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolGenData.AgentCode"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolPayData.PolID"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolPayData.FirstStatementDate"
    gSQLCmd_str = gSQLCmd_str & ", Sum((IIf([PaymentMethod] Like 'A',[PHPrem]-[BrokerComm],-[BrokerComm]))) AS [AS Due]"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolPayData.AgencyType"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolPayData.State"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolGenData.Product"
    gSQLCmd_str = gSQLCmd_str & ", Null AS Handler"
    gSQLCmd_str = gSQLCmd_str & ", Null AS CreditTerms"
    gSQLCmd_str = gSQLCmd_str & ", Null AS TradeArea"
    gSQLCmd_str = gSQLCmd_str & ", Null AS BrokerName"
    gSQLCmd_str = gSQLCmd_str & " INTO tabtempReport"
    gSQLCmd_str = gSQLCmd_str & " FROM AS_PolPayData"
    gSQLCmd_str = gSQLCmd_str & " INNER JOIN AS_PolGenData ON AS_PolPayData.PolID = AS_PolGenData.PolID"
    gSQLCmd_str = gSQLCmd_str & " GROUP BY AS_PolPayData.EffectiveStartDate"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolPayData.Comments"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolGenData.AgentCode"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolPayData.PolID"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolPayData.FirstStatementDate"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolPayData.AgencyType"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolPayData.State"
    gSQLCmd_str = gSQLCmd_str & ", AS_PolGenData.Product"
    gSQLCmd_str = gSQLCmd_str & " HAVING ((AS_PolPayData.AgencyType " & lPolPayAType_str & ")"
    gSQLCmd_str = gSQLCmd_str & " AND (AS_PolPayData.State =" & lPolPayState_int & ")"
    gSQLCmd_str = gSQLCmd_str & ");"
    DoCmd.RunSQL gSQLCmd_str
    'CurrentDb.Execute gSQLCmd_str
    
    DoCmd.OpenQuery "qryUpdate_TempReport_CBT"
    DoCmd.OpenQuery "qryUpdate_TempReport_LookUp"
    
    If IsNull(Me.OptHandler) = False Then
        DoCmd.OpenQuery "qryDelete_TempReport_UnwantedHandler"
    End If
   
Exit_CreateReportTable:
    DoCmd.SetWarnings True
    Exit Function

Err_CreateReportTable:
    MsgBox Err.Description
    Resume Exit_CreateReportTable
    Resume
    
End Function

Thanks

and then the recordsource is

SELECT tabtempReport.AgentCode, tabtempReport.BrokerName, 
tabtempReport.Handler, tabtempReport.CreditTerms, Sum(tabtempReport.[Number of Trans]) AS [SumOfNumber of Trans], Sum(tabtempReport.[AS Due]) AS [SumOfAS Due], tabtempReport.TradeArea FROM tabtempReport GROUP BY tabtempReport.AgentCode, tabtempReport.BrokerName, tabtempReport.Handler, tabtempReport.CreditTerms,