Hi. I'm wondering if someone would please let me know if there is a better way to run the loop in the following code. My code is extremely slow on a core 2 duo 3.16GHz cpu with 2G RAM. My goal is to loop through a table of approx 5000 records, perform a calculation for a field and update that field for the current record, then move to the next record.
Code:
' Set the recordset the the appropriate table
Set rs1 = CurrentDb.OpenRecordset("t_POCheckAllItemsCalculations", dbOpenDynaset)
' Select first record in the recordset
rs1.MoveFirst
' Loop until it is not the end of the recordset
Do While Not rs1.EOF
' Reset variables
' Fill variable with the current date
theDate = Date
' Fill variable with the current day of the week
theWkDay = Format(theDate, "dddd")
' Set temp variable to 1
tempVar = 1
' Set cases to be sold to 0
qCsToBeSold = 0
' Get the current record number
curRecord1 = rs1.AbsolutePosition + 1
On Error Resume Next
' Loop through until the days until delivery has been reached
Do While tempVar <= rs1.Fields("[Days Until Delivery]").Value + 1
' Look up the value from the table using a formula
qCsToBeSold = qCsToBeSold + DLookup("[" & theWkDay & " Average]", "[t_CombinedVelocityTotals]", "[Item Code] = " & rs1.Fields("[Item Code]").Value)
' Add one to the date variable in order to move to the next day of the week
theDate = theDate + 1
' Extract the day of the week from the date variable
theWkDay = Format(theDate, "dddd")
' Add 1 to the temp variable to use as a counter
tempVar = tempVar + 1
Loop
' Populate the table with the records
With rs1
.Edit
![Cases to be Sold] = qCsToBeSold
End With
' Update the table
rs1.Update
' Move to next record
rs1.MoveNext
Loop
