Access - Autoexec macro only run during a certain time

[MM1963]

I’m using Access 2016. I have an autoexec macro that runs every time the database is opened. How can I set a condition that only executes the autoexec macro if the database is opened between 5:45 AM and 6:15 AM? Thanks for any help.

 

[dhookom]

You always run the macro but call a function that uses the time in a If Then condition. I don't use macros but I expect there is some conditional branching that can be used.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Time is just a number, a portion of date.

Try something like:

[green]
'5:45 AM is 0.2395833 and 6:15 AM is 0.2604167
[/green]
Dim currTime As Single
currTime = Time

If currTime > 0.2395833 And currTime < 0.2604167 Then
  [green]  'Run your Macro[/green]
Else
[green]    'Do Nothing[/green]
End If

---- Andy

There is a great need for a sarcasm font.

 

[MM1963]

Andrzejek, thank you very much. works perfect. I was not sure how to indicate the time.

 

[Andrzejek]

I am glad you've found it helpful.
But...
If you want your autoexec macro to run (I assume) once a day and (I assume) when first user runs your application, I would not set it the way you have it. Because - if most of the users start their work around 6:00 AM, your autoexec macro will run several / many times anyway. And if your first user runs your app at 7:00 AM, your macro would not run at all that day.

What I would do is have a simple table:[pre]
ID DateRun
1 11/23/2018
2 11/24/2018
3 11/25/2018
[/pre]
So instead of the check you have right now, I would check this table if it has today's date in it. If it does, your macro was run already. If it does not have today's date, run your macro, and INSERT a record with today's date into this table. Simple

This way your macro will be triggered by first user of the day, no matter what time first user will run your app. And it will run just once a day.


---- Andy

There is a great need for a sarcasm font.

 

[MM1963]

Thanks again for your input. However, I am the only person that opens this database. I have a batch file that opens the database at 6:00 AM daily and will execute the autoexec macro. This creates export tables and Excel files for other users. If I need to go into this database during the day for some maintenance, I don’t want the autoexec macro to run. I think this code is a better method than remembering to hold down the <shift> key to prevent the autoexec macro from running.
There is one other macro that I only want to run on Wednesday morning between 5:45 AM and 6:15 AM. How would I edit the code to add the day of week as well?
Thanks

 

[Andrzejek]

[tt]Weekday()[/tt] function returns the day number of the Date passed. 1 - Sunday, 2 - Monday, etc. If this function returns 4, that's Wednesday. But I would not hard-code the days of the week, I would use build-in VBA constants:

[pre]
If Weekday(Date) = vbWednesday Then
Debug.Print "BINGO, run your macro"
End If
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[strongm]

>[tt]If currTime > 0.2395833 And currTime < 0.2604167 Then[/tt]

Not quite sure why you wouldn't just do:

[tt]If time > #5:15 AM# and time < #6:15 PM# Then[/tt]

 

[Andrzejek]

That would be easier
It is probably just me. When I see dates or times, I see numbers. Today's noon is not 11/26/2018 12:00 PM, it is rather 43430.5


---- Andy

There is a great need for a sarcasm font.

 

[strongm]

>Today's noon is not 11/26/2018 12:00 PM

Well quite - it's actually 26/11/2018 12:00 PM ;-)

 

[Andrzejek]

I the old days (and old country) that would be a lot less ambiguous and shown as 26 XI 2018 (months were represented in Roman Numerals). I still have to remind myself about this weird (US) date format...


---- Andy

There is a great need for a sarcasm font.