Procedure very slow when triggered from Form (1 Viewer)

jerem · Feb 18, 2016

Hi
I have a sub that is in a standard module. It is triggered by a button placed in a form. If I click on the button, the procedure takes a very long time but if I go into the VBA editor, run the sub while the form is open, it does the trick in no time at all. So I am at loss. Please help! Thank you

JHB · Feb 18, 2016

Could you show the code you've behind the button - use code tags, click the "#".

jerem · Feb 18, 2016

Here you are...

Code:

Private Sub cmdSave_Click()
On Error GoTo ErrHandler
    Application.Echo False
    Call NormalizeIt
    LoadCrosstabForm
    blnDataChangedInSubForm = False
    Me.cmdSave.Enabled = blnDataChangedInSubForm
ExitSub:
    Application.Echo True
    Exit Sub
ErrHandler:
    Call ErrorAlert
    Resume ExitSub
End Sub

It's the NormalizeIt that is an issue. If I remove it, trigger the procedure from the VB editor and then run the above code, it runs very fast.

Here is the code behind Normalizeit

Code:

Public Sub NormalizeIt()
Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset
Dim pr As Parameter
On Error GoTo EH

'delete existing data from temp table
Set db = CurrentDb
db.Execute "DELETE * FROM tblNormalize;", dbFailOnError + dbSeeChanges

'get a recordset of the column headers
Set qd = db.QueryDefs("qryCrossEmployee")
qd.Parameters("[TempVars]![tmpTeamID]") = TempVars("tmpTeamID").Value
qd.Parameters("[TempVars]![tmpYear]") = TempVars("tmpYear").Value
qd.Parameters("[TempVars]![tmpFundID]") = TempVars("tmpFundID").Value
For Each pr In qd.Parameters
    pr.Value = Eval(pr.Name)
Next pr
Set rs = qd.OpenRecordset
rs.MoveFirst
Do While rs.EOF = False
' "un" crosstab the data from tblCrosstab into tblNormalize
    db.Execute "INSERT INTO tblNormalize ( Investment, ID, DealerPct )" & Chr(10) & _
    "SELECT InvestName, " & rs.Fields("ID") & ", [" & rs.Fields("ID") & "]" & Chr(10) & _
    "FROM tblCrosstab;", dbFailOnError + dbSeeChanges
   rs.MoveNext
Loop
rs.Close
qd.Close
Set rs = Nothing

'update the original normalized dataset based on edited dataset
Set qd = db.QueryDefs("qryUpRenormalize")
qd.Parameters("[TempVars]![tmpEmployeeID]") = TempVars("tmpEmployeeID").Value
For Each pr In qd.Parameters
    pr.Value = Eval(pr.Name)
Next pr
qd.Execute dbFailOnError + dbSeeChanges

EX:
 
    Set rs = Nothing
    Set qd = Nothing
    Set db = Nothing
    Exit Sub
EH:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
    Resume EX
End Sub

JHB · Feb 18, 2016

You've a loop in your code, depending of how many records it is tied to the rs recordset - it takes time to execute it because you're also updating the table "tblNormalize". I don't think you can do anything about it.
But how long is "takes a very long time"?

jerem · Feb 18, 2016

I have drilled a bit further. The area that is hanging is when executing the update query "qryUpRenormalize". When I trigger the code from the VBA editor, it takes about 3 sec, when clicking the button in the Form it takes 45 sec.

jerem · Feb 18, 2016

Ok you might be right... I made some changes to that query and it runs faster. However, it still doesn't explain why when the code was called from the VB editor it ran faster.

JHB · Feb 18, 2016

jerem said:
Ok you might be right... I made some changes to that query and it runs faster. However, it still doesn't explain why when the code was called from the VB editor it ran faster.

I don't think all code got executed, (did you put in some break points to follow the execution?), how did you run it from the VB-editor?

arnelgp · Feb 18, 2016

this code is redundant and in fact not needed in your code since you are dealing with tempvars, which is already available:

Code:

qd.Parameters("[TempVars]![tmpTeamID]") = TempVars("tmpTeamID").Value
qd.Parameters("[TempVars]![tmpYear]") = TempVars("tmpYear").Value
qd.Parameters("[TempVars]![tmpFundID]") = TempVars("tmpFundID").Value
For Each pr In qd.Parameters
    pr.Value = Eval(pr.Name)
Next pr

also this one needs to be deleted from your code:

Code:

qd.Parameters("[TempVars]![tmpEmployeeID]") = TempVars("tmpEmployeeID").Value
For Each pr In qd.Parameters
    pr.Value = Eval(pr.Name)
Next pr

Procedure very slow when triggered from Form (1 Viewer)

jerem

Registered User.

JHB

Have been here a while

jerem

Registered User.

JHB

Have been here a while

jerem

Registered User.

jerem

Registered User.

JHB

Have been here a while

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread