Prevent macro from Running Twice in one day?

[bigroo1958]

Hi All,
I have a macro that will run from a form button.
The macro performs various tasks within my database, this macro is run daily with a new data file, I compare the current data file to the previous day's and report on the changes between the two files.
What I want to do is if the macro has been run for example, today already, I don't want to be able to run it again till tomorrow.
Here is the code I have on the import button.

Please Login or Register  to view this content.

Any assistance is greatly appreciated!

[LJN8]

Make your code to save the last run date to somewhere in a worksheet, and save the worksheet. Also at the beginning in your macro to compare between the current date and saved date before any other codes can be executed.

[Tinbendr]

I would store the day the code runs somewhere. A cell, the sheet properties(like the author field) and compare that date with today's date.

[TMS]

Add a hidden Control or Configuration sheet. On that sheet, use a cell, say A1 to hold the last date that the routine was run.

When the routine is run, the first thing it should do is check if the cell contains the current date. If it does, it must exit. If it doesn't, it can store the current date in the cell and continue execution.

Two lines of code.


Regards, TMS

[bigroo1958]

Thanks Guys for the replies!

So, I should setup a table in my database to save the last date run and compare this date with the current date?
I never thought of that, great idea.
Do any of you have example code to do this in Access?

[TMS]

Sorry, I think we all missed that it was an Access query.

Whatever, a simple table with one record/entry would serve the purpose. No code, unfortunately.

Basically, get the record, check it, update it if the date is different, exit or carry on.

[bigroo1958]

Thanks for the reply TMS!

I worked on this some and here is what I have come up with!
I added a new table to hold the date the macro has last run. If the macro has not been run today, I have a doCmd RunSql update statement to enter in current date into the table. The problem I am having is the comparison from date() to date in table, not quite working right. Can someone point me in the right direction or am I over thinking this???
I don't think I have the if statements correct!

Please Login or Register  to view this content.

[bigroo1958]

After further testing, I think this works for me!!

Please Login or Register  to view this content.

[Vikas_Gautam]

Here is the code you can try:-
I have used Registry settings to get the required job done.
Use the following sub-routine to initialize your main function. (Untested though)

Please Login or Register  to view this content.

[bigroo1958]

Thanks so much Vikas for the reply,

This is not in MS Excel but MS Access VBA, will your code example work in Access?

[Vikas_Gautam]

Yes Bigroo, The above code does work with Access ( Since I have made some changes now ).
The reason is that I haven't use any excel object here.
Check the attached Access file ( It contains the code may be, as its my first time with Access Vba Module. Ha Ha )

[bigroo1958]

Thanks Vikas for the reply and database example!!

Also, thanks to everyone else who stopped by and replied, I appreciate the input!!
Thanks