The following code clip is something that I have in place to generate a report based on sent parameters to a stored procedure. The difficulty I am having is that the stored procedure is quite complex and takes about 10 seconds to execute. When the sp is changed, it seems that Access is re-compiling the query before actually running it. The amount of time it takes to 're-compile' is directly in proportion to the length of time it takes to run the actual query. the problem I have is that Access runs the routine to print the report before the query is 're-compiled' and executed, thus causing an error. Is there any way force Access to re-compile the sp, or have a smart waiting period time that would only release when the process is complete? I simple for/next loop (as you see below) is too kludgy. By the way, DoEvents does not seem to acheive this for me.
Private Sub cmdBlockListbyPort_Click()
On Error GoTo Err_cmdBlockListbyPort_Click
Dim a As Long
Dim rpt As Report
Dim db As Database
Dim qryDef As QueryDef
Dim QryName As String
Dim NumCopies, X As Integer
Dim frm As Form, ctl As Control
Dim varItem As Variant
Set frm = Forms!frmReports
Set ctl = frm!lstbPortfolioName
If ctl.Selected(0) Then
If MsgBox("You cannot select All Portfolios for this report", vbOKOnly, "Report Error") = vbOK Then
For Each varItem In ctl.ItemsSelected
Set db = CurrentDb()
QryName = "spBlockListbyPortfolio"
On Error Resume Next
On Error GoTo 0
Set qryDef = db.CreateQueryDef(QryName)
qryDef.Connect = "ODBC;DATABASE=Blotter;DSN=Blotter"
qryDef.ReturnsRecords = True
qryDef.SQL = "EXEC Blotter..Q_ReportBlockList '" & Forms!frmTradingRoom!txtForDate & "', '" & ctl.ItemData(varItem) & "'"
For a = 1 To 10000
Call PrintReport("rptBlockList", NumCopies)