luscus wrote:
Thanks for all the responses on my first question. Unfortunately the
answers I was given were too complicated for my small brain , and
neophite condition to understand. So if you could talk down to me and
write in easier terms I would be very gratefull to all you access
wizards!
Here is my problem. I have a table with maybe 10 fields, It is used to
imput information taken over the phone to solve patient problems in our
Spanish Department. So it has Fields like: Date, Time, Patient Name,
Provider Name, Comments. Now here is where I need help.
At the End of the table I have the following Fields: End Time, Total
Time, Done. I was thinking that once I have finished solving a patients
problem (could be 5 minutes, or 20 ) I would check a yes/no box (Done)
and that would set off two orders;
1- to tell the End Field to stamp the time at that moment
2- to Tell the Total Time field How many minutes I spent on that one
entry.
I got 5 different answers and I understood none of them, becouse I am
very green at Access:
I got one that said:
In the On Change event of the "Done" Checkbox (Whhhhhaaaaat? I looked
everywhere for the On Change event never found it)
Create an unbound text box on your form ( I am not working with a form,
and I have no Idea what an unbound text box is)
I tried inserting Me![End Time}=Now() in the Default value,and then in
Validation rule, of that field but access said No,no,no can't do
that???? why?
I Know that being such a neophite makes it difficult to help, but if
you could I would apreciate simple answers and if you use terms like On
change, tell me where to find it, or send me a print screen of where it
is. at the above email or to ma******@BIDMC.HARVARD.EDU
Thanks a lot to all.
Manuel
This is what Tom said"
--------
In the checkbox' AfterUpdate event write one line:
txtEndTime = Time
(replace txtEndTime with the name of your text box)
Total Time is a different story. It is what's called a Calculated
Field, and should not be stored in the database. Rather you
recalculate it when you need it, typically in a query.
So you create a new query, select your table, drag some fields to the
grid, and in a new field write:
TotalTimeInMinutes: DateDiff("n",BeginTime, EndTime)
(replace BeginTime and EndTime with your field names)
-------
1. Click on the control for the checkbox Done. A popup menu will open.
2. Select Property. this will open a property box.
3. select the event "AfterUpdate" and click the ... to the right
of the afterupdate entry. This will open a VBA window that
will allow you to enter VBA code.
4. Enter the following replacing the name Tom used with your name
for the end time.
txtEndTime = Time
Replace it with Me![End Time} = Time
Each time the checkbox is changed, this code will run.
5. Then add the following to the same event
TotalTimeInMinutes: DateDiff("n",BeginTime, EndTime)
Replace the name with:
Me![Total Time]= DateDiff("n",Me![Begin Time], Me![End Time])
6. When completed the code should look like this.
Me![End Time} = Time
Me![Total Time]= DateDiff("n",Me![Begin Time], Me![End Time])
What Tom said about a calculated fields in the database is good
advice. Go ahead and do what you want to do so you can learn
how the afterupdate event and other events work. Then when you
get an error in your database you will learn or understand why
you should not put calculated fields in your table.
Example: If you or others review the records you have created
and manually change the start time or end time , then your
calculated time difference in Total Time will be wrong.
This can be done by opening the table is datasheet and changing
the values.
The total time can be calculated in a query for other forms or
reports to use. Then if some one changes the start and/or end time
it will still calculate the correct total time.
In this form, total time should be an unbound control. That means
it should not be linked to a field in the table the form is linked
to. It the OnCurrent record of the form you can add the following code
to recalculate the total time of existing records.
Me![Total Time]= DateDiff("n",Me![Begin Time], Me![End Time])
You will have to add error checking to your code to check for
the following.
1. The checkbox is ture or done.
2. Start time is a valid time.
3. End time is a valid time.
4. End time is not before start time unless time was was from the
day before.
Hope this helps.
Ron
--
Ronald W. Roberts
Roberts Communication
rw*@robcom.com