instructions about how the form is supposed to be used/work, to my email
address (obtain it by removing this is not real from the address).
I'll take a look at it as time permits.
--
Ken Snell
<MS ACCESS MVP>
Is there anyway I can maybe send you a copy of what I have
so you can look at it?
-----Original Message-----
How can the value of "Approved ECD" field change? Does it
change when a user
changes it? When you open the form on a new day
(different "current date")?
Does some other event change it? You need to determine
what is happening
when it "changes" if you want to "capture" that change so
that you can do
something in the other controls.
Otherwise, you need to recalculate the values of
Total_Behind_Schedule and
Time_Left_to_Close_Date controls whenever you open the
form or change to a
different record. Then you don't need to "capture" the
change of Approved
ECD. Can you identify an expression that would result in
the correct
calculation each time? Then you could use that expression
as the control
source for the controls.
I am having difficulty visualizing your form and table
setup -- that is
hampering my ability to "see" a possible solution. I hope
you're not trying
to store the values of Total_Behind_Schedule and
Time_Left_to_Close_Date
controls in a table -- if they can be calculated, then
you should calculate
them when you want to see them. Then my suggestion of
using an expression as
the control source for these two controls makes even
better sense.
As for having them show up only if positive, your
expression could utilize a
statement similar to this; for example,
IIf([Status] <> "Closed", IIf(DateDiff("d", Now
(), [Approved ECD]) >
0, DateDiff("d", Now(), [Approved ECD]), Null), Null)
--
Ken Snell
<MS ACCESS MVP>
Ken,
Let me tell you what I am trying to do. I have a
database in which I am tracking some taskings with. I
would like to show either how many days left till the
close of the particular task or how many days past the
ECD
(estimated close date). This is where the time left to
close and the behind schedule fields come in. What I
would like to be able to do is have the above 2 fields
update automatically when and if the approved ECD field
changes. Since I placed the code under the AfterUpdate
I
don't think it will up date unless the Status field is
touched. Is there an easy way of doing this?
I would also like to have the time left to close and
behind schedule fields to only show up they are positive
numbers. for example if the time left to close date
shows
a positive number and the behind schedule date shows a
negative number then the behind schedule field will be
null. Is this possible to do as well?
-----Original Message-----
Assuming that you're describing a control that is
displaying the value of
the "total behind schedule" field, you can use a line
of
code similar to
what you used in your original code when the Status
control was "Closed":
Me.Total_Behind_Schedule = Null
Here would be the modified code:
Public Sub Status_AfterUpdate()
If Me.Status = "Closed" Then
Me.Time_Left_to_Close_Date = Null
Else
Me.Time_Left_to_Close_Date = DateDiff("d", Now (),
[Approved ECD])
Me.Total_Behind_Schedule = Null
End If
End Sub
--
Ken Snell
<MS ACCESS MVP>
Ken,
Something has come along the same line as this one.
If
you can help that would be great. I got the code to
work
for what I needed. Now i get a number in the time
left
to
close field and the total behind schedule field.
Lets
say
26 is the number. Time left to close say 26 and the
total
behind schedule says -26. I want to put some code
into
the database that tells it to null the field that is
negative.
Also,
When the Approved ECD fields date changes I would
like
to see the numbers in the fields mentioned above to
change. How can I do this?
-----Original Message-----
Ken,
Thanks for the help. I don't know why but the
code
would not work for the on exit but works just fine
for
the
after update. I thought the code should work that
is
where I was getting confused. Again thanks for the
help.
-----Original Message-----
But that is what I understand that your code is
doing.
Is
it not working?
If not, try moving it to the AfterUpdate event of
the
Status field.
Sorry, but I am not understanding completely, I
think.
--
Ken Snell
<MS ACCESS MVP>
This is what happens when I exit the status
field.
When
it says closed and I tab out of the field I get a
number
that pops up in it. I want it to look at the
status
field
and if it sees anything but the word closed to
then
use
the datediff formula. If the word closed shows
up
in
the
status field then I want it to leave the Time
left
to
close field empty.
-----Original Message-----
I am not understanding what you want to do that
is
different from what you
have posted as your code? Can you elaborate,
please?
--
Ken Snell
<MS ACCESS MVP>
[email protected]...
I have 3 fields one of which is called Status,
one
called
Approved ECD and another called Time left to
close.
I
need to find out how to get the time left to
close
field
to place a number of days left till the date
in
the
approved ECD field IF the status field does
not
say
closed. If it does say closed I would like
the
time
left
to close field to become empty or null.
Below is
what I
have:
Public Sub Status_Exit(Cancel As Integer)
If Me.Status = "Closed" Then
Me.Time_Left_to_Close_Date = Null
Else
Me.Time_Left_to_Close_Date = DateDiff ("d",
Now
(),
[Approved ECD])
End If
End Sub
Any help would be greatly appreciated.
