code speed depends how many time I run it

[paulmcdonnell]

Hi guys!!!!

I have some code which loops through a ercord set and peforms an action if a certain criteria is met. Then is continues looping until the end of the records.

The code is not structurally great but it works, however what I've noticed is that When I open the program and run the code behind a form it takes a few minutes to complete. So i display a please wait form to inform the user.

If i open the form a second time after the routine has run the next run takes seconds (about 1/10 of the time of the first).

I understand this can be because access reads and runs the code first, like the advantage of running queries faster by saving them as queries so access maps them out.

How can i speed up the first instnace of this code run. I though of running the code before the application starts, so that when it runs properly, it's actually on it's second run and hence goes more quickly.


Anybody have any ideas, this one really would help.

Cheers
Paul

Code:

Dim name as string , dob as date, send As Date, Action as string, tit as string, ema As String
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Query_Service birthday warnings")
rst.MoveFirst
Do While Not rst.EOF
If rst("date to issue") <= Date And IsNull(rst("date issued")) Then GoSub warnme
rst.MoveNext
Loop
GoTo Done
End

warnme sends an email to the remote administrator using docmd.sendobject.
Even using mapi the code still runs many times faster on it's second run

 

[Fornatian]

Use a for...next loop to run the code twice, use a counter to identify how many times the loop has run, using an if statement only run the email sender if the counter is above the record count, thus it must be on the second run.

 

[Opengrave]

I've often made the mistake of not compiling the application. Have you gone into the code window and 'compile and save all modules'. This sounds silly but it has taken care of my performance problems more than a few times.