MS Access 2007

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??

:o

What kind of code is the button running?

when the button is clicked it runs

Code:

---

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

Code:

---


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,

---