Timer Count Down

[Stockwell43]

Hello,

Is there a way of placing a timer on a form that, when the start button is
clicked it will start counting down from a specified time?

Example:

Say I click the button and it starts to count down, I want the timer
displayed to show people how much time is left to bid on an item. This way,
when everyone opens the database it will always show the current countdown
time and not refresh itself. Say a count down time of 2 days?

Is this possible?

Thanks!!

 

[Douglas J. Steele]

Forms in Access have a built-in Timer object. To activate it, you set the
form's TimerInterval to the number of milliseconds you want between
invocations of the form's Timer event. In other words, if you want the Timer
event to fire every 30 seconds, you set the form's TimerInterval property to
30000.

The TimerInterval is a Long Integer, which means that the maximum value to
which you can set it is 2,147,483,647 (which corresponds to almost 25
days!). However, I suspect what you'll want is to set it to something small
(say 1000, for 1 second) and put code in the Timer event to keep track of
how much time is remaining and display that information on the form.

 

[Stockwell43]

Hi Doug,

I appreciate your response.

I understand 600000 equals one minute. I understand your first part of the
Time interval being on the form. But what code would I use to count it down
and show it on the form so whenever the user opens the form it will show the
remaining time without resetting?

Thanks!!

 

[Douglas J. Steele]

You'd need to store the expiration time in a table (let's make it simple: a
table named Countdown, with a field named ExpiryDtm. The table either has a
value in it, or it doesn't.)

In the form's Open event, you can check whether there's a value in that
table. If there isn't, set Me.TimerInterval to 0 (since you don't need the
timer to be active). If there is, set Me.TimerInterval to 60000 (not 600000:
that's 10 minutes.)

Private Sub Form_Open(Cancel As Integer)
Dim varExpiryDtm As Variant

varExpiryDtm = DLookup("ExpiryDtm", "Countdown")
If IsNull(varExpiryDtm) Then
Me.TimerInterval = 0
Else
Me.TimerInterval = 60000
End If

End Sub

Inside the form's Timer event, put code like:

Private Sub Form_Timer()
Dim varExpiryDtm As Variant

varExpiryDtm = DLookup("ExpiryDtm", "Countdown")
If IsNull(varExpiryDtm) Then
Me.TimerInterval = 0
Else
Me.txtTimeRemaining = DateDiff("s", Now(), varExpiryDtm) & " seconds
remaining."
End If

End Sub

(I'm assuming there's a text box named txtTimeRemaining on the form.)

 

[Stockwell43]

I can't seem to get it to work. I followed everything you wrote and when I
place the code in I get a Compile Error:
Expected: Indentifier or bracketed expression.

The word remaining is red and the " is highlighted. Did I do something
wrong? Sorry but my coding is not up to snuff. )

 

[Douglas J. Steele]

Sorry, you're the victim of word-wrap in the response.

That msgbox line should be all one line.

This should be correct without word-wrap:

Private Sub Form_Timer()
Dim varExpiryDtm As Variant

varExpiryDtm = DLookup("ExpiryDtm", "Countdown")
If IsNull(varExpiryDtm) Then
Me.TimerInterval = 0
Else
Me.txtTimeRemaining = DateDiff("s", Now(), varExpiryDtm) & _
" seconds remaining."
End If

End Sub

(note that there must be a space in front of that underscore character.)

 

[Stockwell43]

Hi Doug,

Thank you for the correction, I replaced the previous code.

When look at the form in form view, nothing happens.

I have the table with the field you mentioned, added the code to Form Open
and Timer Event, set timer interval at 60000 and table to 60000 and have a
unbound text boxed with the same name as in your response.

Did I forget to do something? If I am suppose to place a start button on the
form is there code I use that will activate what's already there?

Sorry about this but, I can never get the hang of this timer stuff.

Thanks!!

 

[Douglas J. Steele]

The code I gave you only does anything if there's a valid date in the
ExpiryDtm field in table Countdown when the form opens. Is there?

How are you planning on putting data into that table? Assuming it'll be
through an event on your form, you'll need to rerun the code in the form's
Open event once the table is populated.

 

[Stockwell43]

Well, I have the field in the table as a Date/Time and in the table I put
60000. Should I be using an actual date like 03/21/2008?

I plan to place a date in the table manually. In otherwords, if today the
auction started and it ended friday I would put 03/21/2008 or whatever the
interval come out to be for that date and place that number in there.

I don't know what you mean about running it through the form or rerunning
the code. I apologize for seeming numb but I never did anything like this
before so I am not familar with this timing thing.

Thanks for being patient and hanging with me!

 

[Douglas J. Steele]

Yes, you should put an actual date in the table field (in fact, you should
put a date and time there). Are you sure it's a date/time field though?
Putting 60000 into a date/time field should result in an error.

What I was trying to say is that you only need the timer when you actually
have a target end date/time. If your countdown form is open and you add a
target end date/time to the table, somehow you need to ensure that the
countdown form knows that there's now a target to count down to.

 

[Stockwell43]

Yes, it is a Date/Time setting. I had it set for Number when I used 60000 but
changes it figuring it was incorrect.

My main form would be my countdown form as I want people to see when the
poen it how much time is left before the auction is ending. However, I'm
having a problem ensure my form knows there is a time in the table. When
place a time in the table and open the form, I don't get any errors but the
txtTime Remaining field is blank. This is pretty much where I am at.

Did I mess something up? I believe I followed everything to a Tee but now
stuck.

 

[Douglas J. Steele]

Try putting a break-point inside the Timer event.

(If you're not familiar with break points, what you do is go into the VBA
module and click in the left-hand margin beside a line of code that actually
does something. My recommendation would be click beside the line
"varExpiryDtm = DLookup("ExpiryDtm", "Countdown")". Assuming you clicked in
the correct place, the line of code should be highlighted.)

Now open your form. Assuming things are working correctly, after the first
minute, you should be taken into the module, with the line of code you
selected highlighted in yellow. Use the F8 key to move execution from line
to line of code.

 

[Stockwell43]

Worked fine. I clicked and got a red dot on the left side. Closed the window,
opened the form and it took me right to that piece highlighted in yellow. I
hit F8 and it went through the code but skipped over line Me.TimerInterval =
0 and then ended when I got to End sub.

 

[Douglas J. Steele]

Okay, if I'm understanding you correctly, it actually executed the step

Me.txtTimeRemaining = DateDiff("s", Now(), varExpiryDtm) & _
" seconds remaining."

and nothing appeared in the text box.

Try putting a DoEvents immediately after that line (before the End If). If
you hold the cursor over varExpiryDtm when the code's running, does it show
the correct value? While the code's running, go to the Immediate Window
(Ctrl-G), type

?DateDiff("s", Now(), varExpiryDtm)

(including the question mark) and hit Enter. What appears in the line below?

 

[Stockwell43]

Hi Doug,

Ok, I placed DoEvent before End If and the piece with the ? in the Immediate
Window (not sure what you meant by holding the cursor while running the
code). Anyway, when I open the form the field is blank and after one minute I
get this:
"-30302 Seconds Remaining" and every minute that passes, it increases by 60
so after one minute it will now say: "-30362 Seconds Remaining". It's getting
close.

 

[Douglas J. Steele]

But that text isn't showing up in the text box on your form, eh?

The fact that the number is negative and increasing suggests that
varExpiryDtm is in the past, not in the future.

Incidentally, I just noticed that there's no code to turn the timer off once
the target date/time is reached. The code should probably be

Private Sub Form_Timer()
Dim varExpiryDtm As Variant

varExpiryDtm = DLookup("ExpiryDtm", "Countdown")
If IsNull(varExpiryDtm) Then
Me.txtTimeRemaining = "No event to countdown."
Me.TimerInterval = 0
Else
If Now() >= varExpiryDtm Then
Me.txtTimeRemaining = varExpiryDtm & " has passed."
Me.TimerInterval = 0
Else
Me.txtTimeRemaining = DateDiff("s", Now(), varExpiryDtm) & _
" seconds remaining."
End If
End If

DoEvents

End Sub

(Don't worry about the cursor bit. I was trying to determine whether a value
was actually being seen for varExpiryDtm, but your tests imply that one is.
What I had meant was after you'd hit F8 when the varExpiryDtm = DLookup line
was highlighted, I simply wanted you to hold your mouse cursor over the name
of the variable, and a tool tips-type window should appear showing the
current value of the variable.)

 

[Stockwell43]

Looks like you did it Doug, you're a genius!

I had the table set to 03/20/2008 and the text box said 03/20/2008 has
passed. I changed the date to 03/21/2008 and it now said 52195 seconds
remaining then went down to 52135 seconds remaining. I only have two
questions and it's not a problem if it can't be done.

1. Is there a way to make the reamining time appear when the form opens
instead of waiting a minute for it to appear?

2. Is the a way to change it to minutes instead of seconds?

Thank you so much for hanging in there and giving me the support. I saved
the code in my code book for future use.

 

[Douglas J. Steele]

To display the time immediately, change your Form_Open event to

Private Sub Form_Open(Cancel As Integer)
Dim varExpiryDtm As Variant

varExpiryDtm = DLookup("ExpiryDtm", "Countdown")
If IsNull(varExpiryDtm) Then
Me.TimerInterval = 0
Else
Me.TimerInterval = 60000
End If
Call Form_Timer

End Sub

To see minutes instead of seconds, use

Me.txtTimeRemaining = DateDiff("n", Now(), varExpiryDtm) & _
" seconds remaining."

(yes, n for minutes, because m is used for months).

Note, though, that that counts how many "different minutes" there are
between the two times. Take a look at the following:

?DateDiff("n", #2008-03-20 12:01:01#, #2008-03-20 12:02:59#)
1
?DateDiff("n", #2008-03-20 12:01:59#, #2008-03-20 12:02:01#)
1

Both will show as 1 minute, even though the first two times are 118 seconds
apart, while the second two times as 2 seconds apart.

 

[Douglas J. Steele]

Oops, the second answer should, of course, have been

Me.txtTimeRemaining = DateDiff("n", Now(), varExpiryDtm) & _
" minutes remaining."

 

[Stockwell43]

Hi Doug,

Everything works great!!! Thank you so much for all your help and for being
extremely patient through this process with me!!