Advice on my simple code please

[CanadianAccessUser]

Hi,

New to VBA here...
Will this code shut down my database at 2AM each night?

Private Sub Form_Current()
If Time$() = 7200 Then
DoCmd.Quit
End Sub

I have it set to On Current in my navigation page...

Thanks,
Canadian

 

[KenHigg]

No..........

 

[pr2-eugin]

No ! It will not, you need the Form Timer method !

Form Current is triggered only once during the lifetime of the Form. On Timer on the other hand will be called every "n" seconds.

So I would suggest you set the timer property as 1800000, (30 minutes * 60 seconds in a minute * 1000 milliseconds). Then on the OnTimer method use something like.

Private Sub Form_Timer()
    If Time() >= #2:00:00 AM# And Time() <=#3:00:00 AM# Then DoCmd.Quit
End Sub

@KenHigg >> Wow !

No..........

 

[pbaldy]

Another option:

http://www.peterssoftware.com/isd.htm

A slight quibble with "Form Current is triggered only once during the lifetime of the Form": the current event fires when the user changes records, so it can fire more than once. It certainly isn't appropriate for the desired use here though, and the code Paul E posted should work fine.

 

[KenHigg]

I answered the question A bit more effort on the op in my opinon. If you are going to dable in code is it too much to expect them to do a simple test of the code before bothering somebody else?

 

[CanadianAccessUser]

Wow is right... hahahaha

I knew I had something wrong in the planning, just wasn't entirely sure what...
I had played with On Timer, but still wasn't sure how to make it work and was obviously lacking in the logic department... Totally understand the logic now thanks to you guys.
I'll try the code above and let you know how it goes in the AM.

THANK YOU

 

[pr2-eugin]

A slight quibble with "Form Current is triggered only once during the lifetime of the Form": the current event fires when the user changes records, so it can fire more than once.

:banghead::banghead: This is how I feel right now !

Yes, I was thinking of Form Open/Load and then I saw the OP mention current, just went with it. That is True, current is triggered every time the form navigates to a record. I am going to check Peter's code, looks interesting, I think it might be the extension of the Microsoft KB article; Detecting idle time or User Inactivity? Either way, might look at it. Thanks for correcting it Paul.

 

[pbaldy]

Thanks for correcting it Paul.

No worries, we Paul's have to stick together.

Figured you knew the current event could fire more than once, but didn't want someone to read that later and get confused.

 

[CanadianAccessUser]

I was unaware that asking questions was bothering people Ken. I was also unaware that assumptions are appropriate. I did test it, it failed, so I asked the question.

Although your initial answer did crack me up.

 

[KenHigg]

Sorry for being short. Did you get the timer solution to work?

 

[pbaldy]

I did test it, it failed, so I asked the question.

I'll put on my moderator hat here. In Ken's defense, your original post didn't mention this. In the future, that info can help the people answering your questions. Mention that you tried it, and it failed. Specify what "failed" means; if you got an error, what it was, or if it didn't error but didn't do what was intended. As my old mentor would say, "more nouns and verbs".

 

[CanadianAccessUser]

Fair enough, and absolutely no offence taken or intended to be given. I just appreciate the help and any laughs I can get out of my detour filled path to success.

Next time I will mention that it failed, and how it failed.
In this case, my code failed so bad it did nothing... which means no errors or any kind of hint to what I had done wrong. My specialty.

Have a great day, eh! lol
Canadian

PS. I will post here tomorrow to let you know if it worked. Too much on my plate atm to keep messing with testing it when it will test itself tonight.

 

[KenHigg]

Just curious - Why would you want to shut down at a certain time?

 

[CanadianAccessUser]

Because the users are driving me nuts...

Each time I make a change to the database (the boss adds forms and reports all the time) I have to save over the front end and people never close it before they leave at night.
I have a few more complicated solutions, but they're a little over my head for right now. I'll keep playing with it, but as a quick fix I just want it to shut down each night so I know it's closed when I start my day each morning.

sneaky sneaky!

 

[MarkK]

"People" never close it? Each person should have his own front end on his own workstation. Then this problem doesn't arise.

 

[pbaldy]

Each person should have his own front end on his own workstation.

+1. Lots of ways to automate pushing new versions out to each PC.

 

[CanadianAccessUser]

MarkK,
I know how you feel about this, as we have had this discussion before, but that's not how this place is set up.
Everything is on the network, we don't have access to the C:\ drive. IT has locked us out of everything so we HAVE to work off the network.

I know it's not ideal, but it is what it is.

 

[pbaldy]

I'd consider user-specific subfolders so each user can still have their own copy (I did that in a Citrix environment). IMHO you're asking for trouble letting multiple users open the same copy.

 

[KenHigg]

I'm curious what happens if the db tries to close on it's own if several users have it open and say have a record open for edit, etc. Seems it'd corrupt the db and that may be a worse situation...

Interesting idea to have a server folder for each user with their own FE...

 

[BigHappyDaddy]

I am making an assumption, but couldn't the timer event be expanded to check for a "dirty" record and either force a save or cancel before closing the database? That would reduce the possibility of having an edited record opened at database close.