how to re-write this Sub to improve the performance?

P

Paul

I have the following private sub to process the data. It cones the data
from a subform and process the record row by row for each of the case like
"Move", "Sales", "Purchase" etc. It is SLOW especially if there are over
1000's records to process. I can use "insert" query to capture the dataset
to a tmp table but I am not sure how to use the tmp table to process the
following queries. Together there are eight different case senairos, I
copied the first case as follows:

Private Sub btnUpdate_Click()
' Comments :
' Parameters: -
' Modified :
'
' --------------------------------------------------
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim frm As Form
Dim varCriteria1 As Variant 'various Event ID
Dim varCriteria2 As Variant 'InventoryDetailID
Dim varCriteria3 As Variant
Dim varCriteria4 As Variant
Dim varCriteria5 As Variant
Dim strInventoryQuantity As Long
Select Case Me.cboEvent
Case "Move" '1
Set frm = Forms![Frm_Update Event]![Frm_Event Subform].Form
Set db = CurrentDb
Set rs = frm.RecordsetClone
On Error Resume Next
If Not rs.BOF Then
rs.MoveFirst
Do While Not rs.EOF
If rs!Status = True Then
varCriteria1 = rs!MoveID
varCriteria2 = rs!InventoryDetailID
varCriteria5 = rs!InventoryID
DoCmd.SetWarnings False
'Append NEW Move To record to the Tbl_Inventory Detail
DoCmd.RunSQL "INSERT INTO Tbl_Inventory_Detail ( InventoryID,
Location, Quantity, UnitPrice, Status, Comment, UserNotes,
StockNumberNotes ) " & _
"SELECT Tbl_Event.InventoryID, Tbl_Move.NewLocation,
Tbl_Move.Quantity, Tbl_Inventory_Detail.UnitPrice, ""Active"" AS Expr1,
""Initially Created By Move Event, "" & ""Date :"" &
Format(Tbl_Move.MoveDate,""dd-mmm-yy"") & "", Move From Location: "" &
Tbl_Inventory_Detail.Location & "", Quantity: "" & Tbl_Move.Quantity AS
Expr2, Tbl_Move.UserNotes, Tbl_Move.StockNumberNotes " & _
"FROM Tbl_Event INNER JOIN (Tbl_Move INNER JOIN
Tbl_Inventory_Detail ON Tbl_Move.InventoryDetailID =
Tbl_Inventory_Detail.InventoryDetailID) ON Tbl_Event.EventID =
Tbl_Move.EventID " & _
"WHERE (((Tbl_Move.MoveID)= " & varCriteria1 & "));"
'Update Move From Quantity to the Tbl_Inventory Detail
DoCmd.RunSQL "UPDATE (Tbl_Inventory INNER JOIN
Tbl_Inventory_Detail ON Tbl_Inventory.InventoryID =
Tbl_Inventory_Detail.InventoryID) INNER JOIN (Tbl_Event INNER JOIN Tbl_Move
ON Tbl_Event.EventID = Tbl_Move.EventID) ON Tbl_Inventory.InventoryID =
Tbl_Event.InventoryID SET Tbl_Inventory_Detail.Quantity =
[Tbl_Inventory_Detail].[Quantity]-[Tbl_Move].[Quantity] " & _
"WHERE (((Tbl_Inventory_Detail.InventoryDetailID)="
& varCriteria2 & ") AND ((Tbl_Move.MoveID)=" & varCriteria1 & "));"
'Update Date to the Tbl_Move
Debug.Print DMax("InventoryDetailID", "Tbl_Inventory_Detail")
DoCmd.RunSQL "UPDATE Tbl_Move SET
Tbl_Move.MoveToInventoryDetailID=" & DMax("InventoryDetailID",
"Tbl_Inventory_Detail") & " , Tbl_Move.UpdatedDate=Now() " & _
"WHERE ((Tbl_Move.MoveID)=" & _
varCriteria1 & ");"

'Update Inventory Quantity to the Tbl_Move
strInventoryQuantity = DSum("Quantity", "Tbl_Inventory_Detail",
"InventoryID= " & varCriteria5)
DoCmd.RunSQL "UPDATE Tbl_Move SET Tbl_Move.InventoryQuantity=" &
strInventoryQuantity & _
" WHERE ((Tbl_Move.MoveID)=" & _
varCriteria1 & ");"

'Update Status to Inactive to the Tbl_Inventory Detail for zero
Quantity
DoCmd.RunSQL "UPDATE Tbl_Inventory_Detail SET
Tbl_Inventory_Detail.Status = ""Inactive"" " & _
"WHERE (((Tbl_Inventory_Detail.Quantity)=0));"
On Error Resume Next
If DLookup("Quantity", "Tbl_Inventory_Detail",
"InventoryDetailID = " & DMax("InventoryDetailID", "Tbl_Inventory_Detail"))
DLookup("Quantity", "Tbl_Inventory_Detail", "InventoryDetailID = " &
Click to expand...
varCriteria2) Then
Debug.Print DLookup("Quantity", "Tbl_Inventory_Detail",
"InventoryDetailID = " & DMax("InventoryDetailID", "Tbl_Inventory_Detail"))
Debug.Print DLookup("Quantity", "Tbl_Inventory_Detail",
"InventoryDetailID = " & varCriteria2)
Debug.Print DMax("InventoryDetailID",
"Tbl_Inventory_Detail")
DoCmd.RunSQL "UPDATE Tbl_Rerserve SET
Tbl_Rerserve.InventoryDetailID = DMax(""InventoryDetailID"",
""Tbl_Inventory_Detail"") " & _
"WHERE (((Tbl_Rerserve.InventoryDetailID)=" &
varCriteria2 & "));"
End If
DoCmd.SetWarnings True
End If

rs.MoveNext
Loop
End If
 
K

kingston via AccessMonster.com

Try going into debug mode and stepping through the code to see which steps
are taking a long time. My guess is that a lot of time is being eaten up by
the many domain functions you use. Is there a reason why you need to use
Debug.Print so often with a domain function (comment them out to see how much
they slow things down)? Could you not simply identify the first and last ID
in a table transaction? Also, is there a way to do this as a query that
works on all records at once so that you don't need to step through one
record at a time? HTH
I have the following private sub to process the data. It cones the data
from a subform and process the record row by row for each of the case like
"Move", "Sales", "Purchase" etc. It is SLOW especially if there are over
1000's records to process. I can use "insert" query to capture the dataset
to a tmp table but I am not sure how to use the tmp table to process the
following queries. Together there are eight different case senairos, I
copied the first case as follows:

Private Sub btnUpdate_Click()
' Comments :
' Parameters: -
' Modified :
'
' --------------------------------------------------
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim frm As Form
Dim varCriteria1 As Variant 'various Event ID
Dim varCriteria2 As Variant 'InventoryDetailID
Dim varCriteria3 As Variant
Dim varCriteria4 As Variant
Dim varCriteria5 As Variant
Dim strInventoryQuantity As Long
Select Case Me.cboEvent
Case "Move" '1
Set frm = Forms![Frm_Update Event]![Frm_Event Subform].Form
Set db = CurrentDb
Set rs = frm.RecordsetClone
On Error Resume Next
If Not rs.BOF Then
rs.MoveFirst
Do While Not rs.EOF
If rs!Status = True Then
varCriteria1 = rs!MoveID
varCriteria2 = rs!InventoryDetailID
varCriteria5 = rs!InventoryID
DoCmd.SetWarnings False
'Append NEW Move To record to the Tbl_Inventory Detail
DoCmd.RunSQL "INSERT INTO Tbl_Inventory_Detail ( InventoryID,
Location, Quantity, UnitPrice, Status, Comment, UserNotes,
StockNumberNotes ) " & _
"SELECT Tbl_Event.InventoryID, Tbl_Move.NewLocation,
Tbl_Move.Quantity, Tbl_Inventory_Detail.UnitPrice, ""Active"" AS Expr1,
""Initially Created By Move Event, "" & ""Date :"" &
Format(Tbl_Move.MoveDate,""dd-mmm-yy"") & "", Move From Location: "" &
Tbl_Inventory_Detail.Location & "", Quantity: "" & Tbl_Move.Quantity AS
Expr2, Tbl_Move.UserNotes, Tbl_Move.StockNumberNotes " & _
"FROM Tbl_Event INNER JOIN (Tbl_Move INNER JOIN
Tbl_Inventory_Detail ON Tbl_Move.InventoryDetailID =
Tbl_Inventory_Detail.InventoryDetailID) ON Tbl_Event.EventID =
Tbl_Move.EventID " & _
"WHERE (((Tbl_Move.MoveID)= " & varCriteria1 & "));"
'Update Move From Quantity to the Tbl_Inventory Detail
DoCmd.RunSQL "UPDATE (Tbl_Inventory INNER JOIN
Tbl_Inventory_Detail ON Tbl_Inventory.InventoryID =
Tbl_Inventory_Detail.InventoryID) INNER JOIN (Tbl_Event INNER JOIN Tbl_Move
ON Tbl_Event.EventID = Tbl_Move.EventID) ON Tbl_Inventory.InventoryID =
Tbl_Event.InventoryID SET Tbl_Inventory_Detail.Quantity =
[Tbl_Inventory_Detail].[Quantity]-[Tbl_Move].[Quantity] " & _
"WHERE (((Tbl_Inventory_Detail.InventoryDetailID)="
& varCriteria2 & ") AND ((Tbl_Move.MoveID)=" & varCriteria1 & "));"
'Update Date to the Tbl_Move
Debug.Print DMax("InventoryDetailID", "Tbl_Inventory_Detail")
DoCmd.RunSQL "UPDATE Tbl_Move SET
Tbl_Move.MoveToInventoryDetailID=" & DMax("InventoryDetailID",
"Tbl_Inventory_Detail") & " , Tbl_Move.UpdatedDate=Now() " & _
"WHERE ((Tbl_Move.MoveID)=" & _
varCriteria1 & ");"

'Update Inventory Quantity to the Tbl_Move
strInventoryQuantity = DSum("Quantity", "Tbl_Inventory_Detail",
"InventoryID= " & varCriteria5)
DoCmd.RunSQL "UPDATE Tbl_Move SET Tbl_Move.InventoryQuantity=" &
strInventoryQuantity & _
" WHERE ((Tbl_Move.MoveID)=" & _
varCriteria1 & ");"

'Update Status to Inactive to the Tbl_Inventory Detail for zero
Quantity
DoCmd.RunSQL "UPDATE Tbl_Inventory_Detail SET
Tbl_Inventory_Detail.Status = ""Inactive"" " & _
"WHERE (((Tbl_Inventory_Detail.Quantity)=0));"
On Error Resume Next
If DLookup("Quantity", "Tbl_Inventory_Detail",
"InventoryDetailID = " & DMax("InventoryDetailID", "Tbl_Inventory_Detail"))
DLookup("Quantity", "Tbl_Inventory_Detail", "InventoryDetailID = " &
Click to expand...
varCriteria2) Then
Debug.Print DLookup("Quantity", "Tbl_Inventory_Detail",
"InventoryDetailID = " & DMax("InventoryDetailID", "Tbl_Inventory_Detail"))
Debug.Print DLookup("Quantity", "Tbl_Inventory_Detail",
"InventoryDetailID = " & varCriteria2)
Debug.Print DMax("InventoryDetailID",
"Tbl_Inventory_Detail")
DoCmd.RunSQL "UPDATE Tbl_Rerserve SET
Tbl_Rerserve.InventoryDetailID = DMax(""InventoryDetailID"",
""Tbl_Inventory_Detail"") " & _
"WHERE (((Tbl_Rerserve.InventoryDetailID)=" &
varCriteria2 & "));"
End If
DoCmd.SetWarnings True
End If

rs.MoveNext
Loop
End If
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compile error...Please help 1
How to stop Access highlight the text in a combo box after an event 1

Top