Last part: please define the beginning of the next day... is this at user logon, or ????
Sorry about that. In our case the beginning of the next day would be after midnight or say, 1:00 am. User logon would not be good because the admin/users could easily log off sometime during the day and then logon again later in the afternoon.
access has a millisecond timer but it would be sledgehammer vs nut to have this running all the time checking for midnight.
simplest is to set up a separate application that links to any of your main tables that are needed for your midnight job. this app needs a single form that opens when the app starts. the form has all the code for your midnight job in the form_load event. set this app to start each midnight using the windows scheduler whizmo.
you can add refinement... schedule your new app to run every hour to guard against network downs and:
create a table myTable containing a single field: JobRan as Date. after you have created the table, open it just once and add a single record with a date sometime in the past (e.g. 1/1/1990).
private sub form_load()
on error goto err_form_load
dim dabs as dao.database
dim recs as dao.recordset
set dabs = currentdb
set recs = dabs.openrecordset("select * from myTable")
if clng(now()) > clng(recs!jobran) then
'your job has not run today
'put your one-time job code here
'and after your code has sucessfully run:
recs!jobran = now()
'the job has already run today: do nothing
set recs = nothing
set dabs = nothing
'do something intelligent... a msgbox is not intelligent in
'a run-alone application, but is helpful for development
msgbox err.description, vbcritical, "Error running one-time job"
use recs.movelast (instead of recs.movefirst)
use recs.addnew (instead of recs.edit)
...and myTable now logs each successful run of the one-time job