Creating a pop up form or notice

[Mike]

I am running Access 2003 on a Windows XP operating system with a split data
base.
I would like to set up a pop up notice reminding the user to close down the
database before an automatic backup of the database is run. I would suspect
that if the backup utility is to run at 0415hrs., then I would want the
notice to "pop up" at 0410hrs.
I am not familiar with code, modules, or visual basics, so any and all help
is GREATLY appreciated.
Thank You

 

[Nicholas Scarpinato]

This is relatively simple, and with this code you can pop up a message to
every user who has the front end of the database open. You will need a form
that has it's Timer Interval property set. I would suggest using a hidden
form, since all this form will be doing is acting as a clock to determine the
system time. On the forms Properties, set the Timer Interval to 10000 (Timer
Interval is measured in milliseconds, so a Timer Interval of 10000 is one
minute). Then go into the Code Builder for On Timer and insert the following
code:

Dim CurrentTime As Date
DoCmd.SetWarnings False
CurrentTime = Format(Now(), "Short Time") ' This formats the current time
(Now()) as XX:XX AM/PM, i.e. 4:23 PM
If CurrentTime >= #4:10:00 AM# Then
MsgBox "The daily backup is about to begin. Please log off until the backup
is complete.", vbExclamation + vbOKOnly, "Please Log Off."
End If
DoCmd.SetWarnings True


This will display the message at 4:10am (or whatever time you want to set
the message to display).

 

[Harlan]

Nicholas,
It looks to me that any time someone logged on AFTER 4:10 AM, they would get
the warning....
due to the "If CurrentTime >=" code.
Shouldn't it be something like:

If CurrentTime >= #4:10:00 AM# AND CurrentTime >= #4:12:00 AM#

The bracketing between two times allows for the possibility that the seconds
aren't exactly the same when the timer goes off.

Also, this is all dependant on all machines having the correct time.

Another way would be to have a table in the backend with one field and only
one entry in that field (yes or no).
Then have the form with the timer "watch" that field and give the warning if
the field is YES.

So, 5 or 10 minutes before you do the backup, YOU set the field to YES and
then after the backup you return the field to NO.

 

[Nicholas Scarpinato]

Good point Harlan... that line should read:

If CurrentTime Between #4:10:00 AM# And #4:14:00 AM# Then

It might be 'Is Between', I can't remember off the top of my head.

And the only problem with manually setting a field is the simple fact that
we humans tend to forget to do things at times... if someone forgets to set
the field, nobody will get the warning message. Of course you could update
the field it via code, but then the database administrator would have to be
logged in every day at that time in order for the code to run, which is a bit
different from what Mike was looking for as I understood his original post.
(Not to mention that updating the field via code at a specific time is pretty
much the same thing as what I've done here, only this code runs on each
user's machine rather than the developer's.)

I originally went into a much more in-depth piece of code that automatically
shut down the user's front end if it was still open at 4:14 AM, but that
would require creating a second form to display the warning message. If you
use a Msgbox to warn the user that it's time to log off, but the user isn't
there to click OK, the code will pause until the msgbox is closed... which
somewhat defeats the purpose. Technically this bit of code actually should be
written using a "Status Message" form rather than using Msgbox, as a form
could be used to display a message while still allowing the rest of the code
to run behind the scenes. This would also allow the database to close itself
down if the form remained open too long, i.e. the user went home for the day
and forgot to close down the DB.

 

[Mike]

I thnk you all for your input. "Steve" I was using Outlook, but the only
problem I have with it is that the notice is not on the top of all other
windows.
Nicholas - your code helped a lot. THANK YOU. It produced exactly what I
was looking for.
I did have one problem with it though and that is that once I set it all
up, the warning would continue to pop up, even 30 minutes later. Was able to
figure it out. Pasted below is a portion of your code
CurrentTime = Format(Now(), "Short Time") ' This formats the current time
(Now()) as XX:XX AM/PM, i.e. 4:23 PM
If CurrentTime >= #4:10:00 AM# Then
The error was at >= #4:10:00 AM#. Afet changing it to = #4:10:00 AM#, the
pop up would only reappear for that 1 minute duration.
Question: We are running Military Time on our machine, are there any
changes that need to be made to the code, or will it still work without any
problems.

THANKS AGAIN!!!!!!!!!!!!!!

 

[Nicholas Scarpinato]

Well, as I mentioned in my reply to Harlan, if you change the line to read:

If CurrentTime Between #04:10:00 AM# And #04:14:00 AM# Then

You'll get the message at any time between those two times. You can also use
a Global variable to store a "switch", if you will, that you can set to on or
off depending on if the message has already been displayed or not. Put this
line in the header of any module in your code (create a module if you don't
already have one):

Global HasBeenDisplayed As Integer

Then modify the code as follows:

Dim CurrentTime As Date
DoCmd.SetWarnings False
CurrentTime = Format(Now(), "Short Time")
If CurrentTime Between #04:10:00 AM# And #04:14:00 AM# Then
If HasBeenDisplayed = 0 Then
MsgBox "The daily backup is about to begin. Please log off until the backup
is complete.", vbExclamation + vbOKOnly, "Please Log Off."
HasBeenDisplayed = 1' This will disable the message from being displayed again
End If
End If
DoCmd.SetWarnings True

This way, if somebody logs in between 4:10 AM and 4:14 AM, they'll see the
warning message the first time, then they won't see it again until the next
day (assuming they log off as they're supposed to).

 

[Tony Toews [MVP]]

Dim CurrentTime As Date
DoCmd.SetWarnings False
CurrentTime = Format(Now(), "Short Time") ' This formats the current time
(Now()) as XX:XX AM/PM, i.e. 4:23 PM
If CurrentTime >= #4:10:00 AM# Then
MsgBox "The daily backup is about to begin. Please log off until the backup
is complete.", vbExclamation + vbOKOnly, "Please Log Off."
End If
DoCmd.SetWarnings True

Why would you need docmd.setwarnings in there?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[sandrao]

Why would you need docmd.setwarnings in there?

Tony
--
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

When I used the code that was a problem with the code line: If
CurrentTime Between #04:10:00 AM# And #04:14:00 AM# Then
especiall the expression "Between"

 

[Douglas J. Steele]

Couple of things. Formatting a time doesn't change its value at all. Don't
use Now (which includes Date as well as Time): Use Time instead. As well,
you can't use Between in VBA.

If Time() >= #4:10:00 AM# And Time() <= #4:14:00 AM# Then
MsgBox "The daily backup is about to begin. " & _
"Please log off until the backup is complete.", _
vbExclamation + vbOKOnly, "Please Log Off."
End If

or

If Time() >= #04:10:00# And Time() <= #04:14:00# Then
MsgBox "The daily backup is about to begin. " & _
"Please log off until the backup is complete.", _
vbExclamation + vbOKOnly, "Please Log Off."
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



When I used the code that was a problem with the code line: If
CurrentTime Between #04:10:00 AM# And #04:14:00 AM# Then
especiall the expression "Between"

 

[sandrao]

Couple of things. Formatting a time doesn't change its value at all. Don't
use Now (which includes Date as well as Time): Use Time instead. As well,
you can't use Between in VBA.

  If Time() >= #4:10:00 AM# And Time() <= #4:14:00 AM# Then
    MsgBox "The daily backup is about to begin. " & _
      "Please log off until the backup is complete.",  _
      vbExclamation + vbOKOnly, "Please Log Off."
  End If

or

  If Time() >= #04:10:00# And Time() <= #04:14:00# Then
    MsgBox "The daily backup is about to begin. " & _
      "Please log off until the backup is complete.",  _
      vbExclamation + vbOKOnly, "Please Log Off."
  End If

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




When I used the code that was a problem with the code line: If
CurrentTime Between #04:10:00 AM# And #04:14:00 AM# Then
especiall the expression "Between"

It works well. Is there another option vbExclamation to all a Quit
button?

 

[Douglas J. Steele]

Sorry, I don't understand your question.

vbExclamation is in that code to put an Exclamation icon on the message box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Couple of things. Formatting a time doesn't change its value at all. Don't
use Now (which includes Date as well as Time): Use Time instead. As well,
you can't use Between in VBA.

If Time() >= #4:10:00 AM# And Time() <= #4:14:00 AM# Then
MsgBox "The daily backup is about to begin. " & _
"Please log off until the backup is complete.", _
vbExclamation + vbOKOnly, "Please Log Off."
End If

or

If Time() >= #04:10:00# And Time() <= #04:14:00# Then
MsgBox "The daily backup is about to begin. " & _
"Please log off until the backup is complete.", _
vbExclamation + vbOKOnly, "Please Log Off."
End If

It works well. Is there another option vbExclamation to all a Quit
button?

 

[Nicholas Scarpinato]

First off, thank you Douglas for clearing up my code. I forgot about using
the Time() function, normally I need both date and time in my programming
because I do a lot of timestamps on things... and yes, I mixed query language
and VB, I should have known better than to reply to this thread after
midnight, lol.

Sandrao, there's no "Quit" button in vb, although you could easily make a
form with a Quit button and open that form instead of using the Msgbox
command. Or alternately, use the OK/Cancel buttons to do your Quit function
as follows:

First, add this Dim to your existing code:

Dim QuitYesNo as Integer

Then change the code as follows, starting with the Msgbox line:

QuitYesNo = Msgbox "The daily backup is about to begin. Please log off until
the backup is complete.", vbExclamation + vbOKOnly, "Please Log Off."
If QuitYesNo = 6 Then DoCmd.Quit

Msgbox returns an integer value depending on the button pressed. If you use
the default OK/Cancel buttons, the values are 6 if OK is pressed and 7 if
Cancel is pressed. You can assign the Msgbox function to an integer variable
and run an If statement off that variable.