Time Elapse

[Bill]

Hi group,

I am working on color coding a row in a datasheet view based on time
elapsed. So what I want is if the time now is great than 7 minutes then
change the row/property to yellow otherwise leave it white. The form must
refresh on a regular basis as well.

This is what I have but it is not working. For one if used the number code
for the color it changes it to 0.65535 and does not run, if I use the word
"Yellow" it fails as well. Then once I get that, how do I refresh the view
every 30 seconds???

If DateDiff("n", [TimeOpened], Now()) >= 7 Then
Me.GeneralField.[Back Color].65535
Else
Me.GeneralField.[Back Color].16777215
End If

Thanks

 

[John W. Vinson]

Hi group,

I am working on color coding a row in a datasheet view based on time
elapsed. So what I want is if the time now is great than 7 minutes then
change the row/property to yellow otherwise leave it white. The form must
refresh on a regular basis as well.

This is what I have but it is not working. For one if used the number code
for the color it changes it to 0.65535 and does not run, if I use the word
"Yellow" it fails as well. Then once I get that, how do I refresh the view
every 30 seconds???

If DateDiff("n", [TimeOpened], Now()) >= 7 Then
Me.GeneralField.[Back Color].65535
Else
Me.GeneralField.[Back Color].16777215
End If

Thanks

Use the form's Timer event. Set the Timer property to 30000 (thirty thousand
milliseconds, 30 seconds) and put your above code in, *changing the period to
an = sign* - you want to Set the back color property to the color code:

Me.GeneralField.BackColor = 65535

You can also use the Visual Basic color constants vbYellow (not Yellow) and
vbWhite.

 

[Bill]

Thanks for the help! Do I've placed this in the Timer Event and set the
Timer interval to 30000 and it just dont work.... :-(

Here is the actual code I am using:

Private Sub Form_Timer()
If DateDiff("n", [time], Now()) >= 7 Then
Me.incident.BackColor = 65535
Else
Me.incident.BackColor = 16777215
End If
End Sub

Thanks for all the help!

Bill

Hi group,

I am working on color coding a row in a datasheet view based on time
elapsed. So what I want is if the time now is great than 7 minutes then
change the row/property to yellow otherwise leave it white. The form must
refresh on a regular basis as well.

This is what I have but it is not working. For one if used the number code
for the color it changes it to 0.65535 and does not run, if I use the word
"Yellow" it fails as well. Then once I get that, how do I refresh the view
every 30 seconds???

If DateDiff("n", [TimeOpened], Now()) >= 7 Then
Me.GeneralField.[Back Color].65535
Else
Me.GeneralField.[Back Color].16777215
End If

Thanks

Use the form's Timer event. Set the Timer property to 30000 (thirty thousand
milliseconds, 30 seconds) and put your above code in, *changing the period to
an = sign* - you want to Set the back color property to the color code:

Me.GeneralField.BackColor = 65535

You can also use the Visual Basic color constants vbYellow (not Yellow) and
vbWhite.

 

[John W. Vinson]

Thanks for the help! Do I've placed this in the Timer Event and set the
Timer interval to 30000 and it just dont work.... :-(

Here is the actual code I am using:

Private Sub Form_Timer()
If DateDiff("n", [time], Now()) >= 7 Then
Me.incident.BackColor = 65535
Else
Me.incident.BackColor = 16777215
End If
End Sub

Add a line

Me.Repaint

after the End If and before the End Sub.

 

[Bill]

Sorry to be a pain, just never wrote this before and I'm just not getting
it..... I added the repaint and nothing. I'm not sure even the color change
is working as I'm not getting any changes when I open the form nor after 30
sec's.

Code:
Private Sub Form_Timer()
If DateDiff("n", [time], Now()) >= 7 Then
Me.[incident].BackColor = vbYellow
Else
Me.[incident].BackColor = vbWhite
End If
Me.Repaint
End Sub

I added the code to the forms On Current event as well... and still nothing...

Private Sub Form_Current()
If DateDiff("n", [time], Now()) >= 7 Then
Me.[incident].BackColor = vbYellow
Else
Me.[incident].BackColor = vbWhite
End If
Me.Repaint
End Sub

I'm running Office 2003

Thanks

Thanks for the help! Do I've placed this in the Timer Event and set the
Timer interval to 30000 and it just dont work.... :-(

Here is the actual code I am using:

Private Sub Form_Timer()
If DateDiff("n", [time], Now()) >= 7 Then
Me.incident.BackColor = 65535
Else
Me.incident.BackColor = 16777215
End If
End Sub

Add a line

Me.Repaint

after the End If and before the End Sub.

 

[John W. Vinson]

Sorry to be a pain, just never wrote this before and I'm just not getting
it..... I added the repaint and nothing. I'm not sure even the color change
is working as I'm not getting any changes when I open the form nor after 30
sec's.

Code:
Private Sub Form_Timer()
If DateDiff("n", [time], Now()) >= 7 Then
Me.[incident].BackColor = vbYellow
Else
Me.[incident].BackColor = vbWhite
End If
Me.Repaint
End Sub

I added the code to the forms On Current event as well... and still nothing...

Private Sub Form_Current()
If DateDiff("n", [time], Now()) >= 7 Then
Me.[incident].BackColor = vbYellow
Else
Me.[incident].BackColor = vbWhite
End If
Me.Repaint
End Sub

What is [time] (other than a reserved word which should not be used for a
controlname)? At what point and how do you set it to the time that the form
was opened?

 

[Bill]

Oops.

time is the field name which I will now change to dtimed to avoid the
controled word you are mentioning...

"At what point and how do you set it to the time that the form was opened?"
I thought I was setting it via the field time but Im guessing I'm not.

Sorry to be a pain, just never wrote this before and I'm just not getting
it..... I added the repaint and nothing. I'm not sure even the color change
is working as I'm not getting any changes when I open the form nor after 30
sec's.

Code:
Private Sub Form_Timer()
If DateDiff("n", [time], Now()) >= 7 Then
Me.[incident].BackColor = vbYellow
Else
Me.[incident].BackColor = vbWhite
End If
Me.Repaint
End Sub

I added the code to the forms On Current event as well... and still nothing...

Private Sub Form_Current()
If DateDiff("n", [time], Now()) >= 7 Then
Me.[incident].BackColor = vbYellow
Else
Me.[incident].BackColor = vbWhite
End If
Me.Repaint
End Sub

What is [time] (other than a reserved word which should not be used for a
controlname)? At what point and how do you set it to the time that the form
was opened?

 

[John W. Vinson]

Oops.

time is the field name which I will now change to dtimed to avoid the
controled word you are mentioning...

A fieldname in the Table? or a control name on the Form? Or both? That may be
the problme if it's ambiguous.

"At what point and how do you set it to the time that the form was opened?"
I thought I was setting it via the field time but Im guessing I'm not.

There must be SOMETHING that is actually putting a date/time value into the
field [dtimed] for your code to work; otherwise how can DateDiff determine how
many minutes have passed?

If dtimed is a field in the table, there may be many records in the table.
Which record did you have in mind?

Step back a bit and explain the context. What is this form, and what is its
purpose?

 

[Bill]

Ok.

I have a form which collects the basic information Call number [ID], Date
[CallDate], Time [dtimed], Incident [Incident]. The date and time on that
form [CallInput] is set to =Now() to capture the current date and time. That
stores it to the table called [CallLog].

Then I want to keep a form [CallDisplay] open in a datasheet view and
refresh every 30 sec. When it refreshes it must capture any new calls and
evaluate the time input. If it was input greater than 7 minutes ago, then
highlight it yellow. If greater than 10 minutes, highlight red.

Thanks for your help. As I'm sure you have figured out, Im still a newbie at
this.

Thanks

Oops.

time is the field name which I will now change to dtimed to avoid the
controled word you are mentioning...

A fieldname in the Table? or a control name on the Form? Or both? That may be
the problme if it's ambiguous.

"At what point and how do you set it to the time that the form was opened?"
I thought I was setting it via the field time but Im guessing I'm not.

There must be SOMETHING that is actually putting a date/time value into the
field [dtimed] for your code to work; otherwise how can DateDiff determine how
many minutes have passed?

If dtimed is a field in the table, there may be many records in the table.
Which record did you have in mind?

Step back a bit and explain the context. What is this form, and what is its
purpose?

 

[John W. Vinson]

Ok.

I have a form which collects the basic information Call number [ID], Date
[CallDate], Time [dtimed], Incident [Incident]. The date and time on that
form [CallInput] is set to =Now() to capture the current date and time. That
stores it to the table called [CallLog].

Well, if you're filling the [CallDate] and [dtimed] fields with Now(), you're
storing the same bit of information redundantly. There should only be *one*
CallTime field! What's actually in your table if you open it in table
datasheet view?

Worse, if you have the Control Source of the textboxes set to =Now(), that
merely *displays* the current date and time (which you see depends on the
format of the textbox). It doesn't store the date or time anywhere at all,
since the textboxes aren't bound to a table field.

I'd suggest having just the one field, and set its DefaultValue property to
=Now(). This will actually *STORE* the current date and time into your table,
at the moment that the record is first "dirtied".

Then I want to keep a form [CallDisplay] open in a datasheet view and
refresh every 30 sec. When it refreshes it must capture any new calls and
evaluate the time input. If it was input greater than 7 minutes ago, then
highlight it yellow. If greater than 10 minutes, highlight red.

If you're using Access2000 or later, you don't need any code at all. Instead,
use Conditional Formatting. Select the field you want colored; on the menu
select Format... Conditional Formatting. Use an expression

DateDiff("n", [CallTime], Now()) > 7

(or > 10) and set the color there. You might still need a Timer event, with
the timer interval set to 30000 but now just doing a Me.Recalc or Me.Refresh
in the form's Timer event. I haven't experimented with this so I'm not sure
which if either will suit you best - try it and please let us know, and if it
doesn't work I'll experiment some more myself.

 

[Bill]

I worked!! kinda...

Ok so in my timer event I used:
Me.Refresh

In the conditional formating I entered two conditions however it's only
looking at the first.

Condition 1: (Expression is)
DateDiff("n",[dtimed],Now())>7

Condition 2: (Expression is)
DateDiff("n",[dtimed],Now())>10

Since it is finding condition 1 true it is not running condition 2. How can
I loop this?

Thanks

Ok.

I have a form which collects the basic information Call number [ID], Date
[CallDate], Time [dtimed], Incident [Incident]. The date and time on that
form [CallInput] is set to =Now() to capture the current date and time. That
stores it to the table called [CallLog].

Well, if you're filling the [CallDate] and [dtimed] fields with Now(), you're
storing the same bit of information redundantly. There should only be *one*
CallTime field! What's actually in your table if you open it in table
datasheet view?

Worse, if you have the Control Source of the textboxes set to =Now(), that
merely *displays* the current date and time (which you see depends on the
format of the textbox). It doesn't store the date or time anywhere at all,
since the textboxes aren't bound to a table field.

I'd suggest having just the one field, and set its DefaultValue property to
=Now(). This will actually *STORE* the current date and time into your table,
at the moment that the record is first "dirtied".

Then I want to keep a form [CallDisplay] open in a datasheet view and
refresh every 30 sec. When it refreshes it must capture any new calls and
evaluate the time input. If it was input greater than 7 minutes ago, then
highlight it yellow. If greater than 10 minutes, highlight red.

If you're using Access2000 or later, you don't need any code at all. Instead,
use Conditional Formatting. Select the field you want colored; on the menu
select Format... Conditional Formatting. Use an expression

DateDiff("n", [CallTime], Now()) > 7

(or > 10) and set the color there. You might still need a Timer event, with
the timer interval set to 30000 but now just doing a Me.Recalc or Me.Refresh
in the form's Timer event. I haven't experimented with this so I'm not sure
which if either will suit you best - try it and please let us know, and if it
doesn't work I'll experiment some more myself.

 

[Bill]

Also, on the refresh it is not grabbing the new data.

Again, your help is SOOO appreciated.

Thanks

Ok.

I have a form which collects the basic information Call number [ID], Date
[CallDate], Time [dtimed], Incident [Incident]. The date and time on that
form [CallInput] is set to =Now() to capture the current date and time. That
stores it to the table called [CallLog].

Well, if you're filling the [CallDate] and [dtimed] fields with Now(), you're
storing the same bit of information redundantly. There should only be *one*
CallTime field! What's actually in your table if you open it in table
datasheet view?

Worse, if you have the Control Source of the textboxes set to =Now(), that
merely *displays* the current date and time (which you see depends on the
format of the textbox). It doesn't store the date or time anywhere at all,
since the textboxes aren't bound to a table field.

I'd suggest having just the one field, and set its DefaultValue property to
=Now(). This will actually *STORE* the current date and time into your table,
at the moment that the record is first "dirtied".

Then I want to keep a form [CallDisplay] open in a datasheet view and
refresh every 30 sec. When it refreshes it must capture any new calls and
evaluate the time input. If it was input greater than 7 minutes ago, then
highlight it yellow. If greater than 10 minutes, highlight red.

If you're using Access2000 or later, you don't need any code at all. Instead,
use Conditional Formatting. Select the field you want colored; on the menu
select Format... Conditional Formatting. Use an expression

DateDiff("n", [CallTime], Now()) > 7

(or > 10) and set the color there. You might still need a Timer event, with
the timer interval set to 30000 but now just doing a Me.Recalc or Me.Refresh
in the form's Timer event. I haven't experimented with this so I'm not sure
which if either will suit you best - try it and please let us know, and if it
doesn't work I'll experiment some more myself.

 

[John W. Vinson]

I worked!! kinda...

Ok so in my timer event I used:
Me.Refresh

In the conditional formating I entered two conditions however it's only
looking at the first.

Condition 1: (Expression is)
DateDiff("n",[dtimed],Now())>7

Condition 2: (Expression is)
DateDiff("n",[dtimed],Now())>10

Since it is finding condition 1 true it is not running condition 2. How can
I loop this?

Reverse the order of the conditions. If it's >10 then it's certainly > 7!

If that doesn't work use an explicit range for the yellow:

DateDiff("n",[dtimed],Now())>7 AND DateDiff("n",[dtimed],Now())<=10

 

[John W. Vinson]

Also, on the refresh it is not grabbing the new data.

Requery instead, then.

 

[Bill]

After I posted this I did just that. Reversing it worked great. THANK YOU!!!

I worked!! kinda...

Ok so in my timer event I used:
Me.Refresh

In the conditional formating I entered two conditions however it's only
looking at the first.

Condition 1: (Expression is)
DateDiff("n",[dtimed],Now())>7

Condition 2: (Expression is)
DateDiff("n",[dtimed],Now())>10

Since it is finding condition 1 true it is not running condition 2. How can
I loop this?

Reverse the order of the conditions. If it's >10 then it's certainly > 7!

If that doesn't work use an explicit range for the yellow:

DateDiff("n",[dtimed],Now())>7 AND DateDiff("n",[dtimed],Now())<=10

 

[Bill]

The Requery was the ticket. Now it refreshes and grabs any new data out
there. IT WORKS!!!! Thank you for all your help!!!!!