That is one very long process.
You can I suspect (and it wouldn't be a quick process) make a lot of those individual record updates, happen to the entire recordset with suitable criteria.
For example the part at the beginning where you exclude a list of different part numbers, this could be be done in a couple of queries on the whole dataset, and avoid you processing those records completely.
So add a integer field to your data we'll call it Skip - set it's default value to 0, make sure every record has it, and that it's set to zero.
And then you can write a query to update all the records you want to skip in one go before any other processing.
Code:
UPDATE RecordsT
SET SKIP = -1
Where Left(DPartNumber, 6 ) In (("400137" ,"515000","515001" ,"515027" ,"515030" ,"515031" ,"515035" ,"515040" ,"515070" ,"515300" ,"515443" ,"614583" ,"615000" ,"615001" ,"615027" ,"615030" ,"615031" ,"615035" ,"615041" ,"615043" ,"615042" ,"615070" ,"615072" ,"AAT141" )
OR OperationDescription = ""
OR SerialNumber = "123456789
OR Left(SerialNumber,2) = "CV"
I haven't included all the cases but I suspect you'll get the drift.
if you run this before any of the the processing then your subsequent operations only need to pull in records you want.
So the next step you process is to update the RMA number and insert records into your IIRRegister - again do the entire recordset in one hit
So instead of a single line insert of
str = "Insert Into IIRRegisterT (IIRNumber,ReceivingDate,IIRStatus) values ('" & DIIRNumber & "','" & DReceivingDate & "','" & "Closed" & "')"
DoCmd.RunSQL str
Firstly, create a query to generate the desired DIIRNumber for every record:
Code:
SELECT "RMA" & DIIRNumber
FROM RecordsT
WHERE Len(DIIRNumber) = 5
AND Nz(Val(Right(DIIRNumber, 1)), 99) <> 99
AND SKIP = 0
Now you can update every record with a RMA NUMBER where you haven't already set the skip flag.
Does this approach begin to make sense?
This is what we mean by batch processing.