Well, it's a little clearer now, but at the moment I've still got only some
general comments.
I assume that the controls on the form are bound to fields in an underlying
table (or query) with those names for the fields. I also assume that the
tble fields for [Date], [StartTime] and [EndTime] are date/time datatypes.
If not, they should be. [As an aside, are you aware that internally Access
stores a date/time value as a double precision number, with the date being
the integer portion as days since 30 December 1899, and the time being the
decimal portion, as a proportion of 24 hours (eg. 1.75 is 18:00:00 (or 6 PM)
on 31-Dec 1899).] Using "Date" as a fieldname should be avoided - it's a
reserved word in Access (refers to the Date function, which returns the
current system date), and using it as a fieldname is simply asking for
trouble when you start using any expressions or code. Additionally, you do
not need a separate Date field and StartTime field - a single datetime
variable can store all this information (in fact, using Now() to enter the
StartTime is storing both the date and time into the StartTime field).
Also, if you must store both start and end times (see next para), using a
single date/time record makes calculation of duration simple - and not doing
so makes them considerably more complicated ;-). If you want/need it, your
form can display the same data in more than one control, with different
formats (eg. a long date format for a textbox displaying only the date
portion of the data, and a time format for the textbox displaying the start
time).
I guess the important question is "Why bother storing the end time?". If
you are assuming that one sign-on time is the same as the previous sign-off
time, there is no need to store this data at all; it is redundant data. If
you need it (eg. in a separate report to show times worked for particular
employees), you can easily calculate it in a query. The only reason it
might be useful is if an employee leaves before someone else arrives. If
that's the case, and they are actually entering their endtime, you certainly
don't want that overwritten by the next person's start time.
Again, HTH,
Rob
Roby said:
Thanks Rob, I'll try to give you a little more detail now.
What I am doing is creating an employee tracking form for stations. So
simply there is a station that needs to be monitored for every second of
the
day. It's not a payroll thing. Employees relieve each other from this
station. My controls on the form are;
[EmployeeName], [Date], [StartTime] and [EndTime]
You have cleared up the start time for me and it works great. Now that
every
second needs to be accounted for I was wondering if when an employee gets
relieved, thier sign in time (which is automated now) could also be the
end
time of the previous entry. I have the form records sorted by date and
time
(Decending)
I hope this is enuff enty, please le tme know if not. this is my last on
the
list for this database,lol THANKS!!
Rob Parker said:
Roby,
I'm not sure that I understand exactly what you mean. And I don't have
any
details of your table(s) and their fields. And also - and it's very
important that you understand this - unless you have some enforced order
to
the records you are displaying on your form, the concept of "next record"
is
essentially meaningless in an Access database. If you haven't set a sort
field, you cannot determine the next (or previous) record accurately.
Also, what you are asking doesn't seem to make sense to me. Your initial
post said you have a continuous form recording sign-on times; so
presumably
you are recording the person's name also. Do you mean that when a new
person signs in, the previous person is considered as having signed out?
That seems to be too constrictive for any real-world business - for
example,
if the second person signs in early, does the first person get paid less
because he hasn't been there as long as he should have? Or do you mean
that
the end time in the "previous" record for that person is the same as the
start time in their next record - which seems so far-fetched that I don't
even know why I mention it ;-)
For us to help you with this, we need considerably more detail about what
you are trying to do, and exactly how you are attempting to do it.
Rob
Roby said:
Thanks dude that worked great!!! you rock.
Now that I have the start time,lol wondering if you couls help with the
end
time. I would just like the end time to be the same as the start time
in
the
next record. Is this possible?? Thanks in advance if you could
enlighten
me!
:
Hi again Roby,
Sorry, I was assuming you knew about VBA code. What you need to do
is:
1. In the property box for the form, select [Event Procedure] in
the
Before Insert event box (you can do this via the drop-down).
2. Click the "..." symbol at the right of the Before Insert event;
this
will open the Visual Basic editor, with a "stub" procedure, thus:
Private Sub Form_BeforeInsert(Cancel As Integer)
End Sub
3. Paste the code in the blank area between these two lines. Make
sure
that you change the "NameOfYourDateTimeField" section of the code to
the
actual name of your date/time field. For example, if your field is
named
SignOnTime, you will finish up with something like:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!SignOnTime = Now()
End Sub
4. Save the changes to the form design.
HTH,
Rob
Good day Rob;
I did what you siad took the default value out of the text box and
added
the
below code to the BeforeInsert. But it came up with an error saying
"Microsoft Access can't fidn the macro" Do you know what this
means??
Thanks!!
:
Hi Roby,
Remove the default value (from either the underlying table, or the
textbox
control), and use the BeforeInsert event of the form (not the
textbox
control) to set the value. The code you need is simply:
Me!NameOfYourDateTimeField = Now()
HTH,
Rob
I have a continuious form that records sign in times for
employees.
My
problem arrises when an employee puts thier name in the control a
new
record
appears for the next person with the current time on it as I have
it
set
to
Defalt Value =Now(). So 20 minuites later when another employee
signs
in
the
time is wrong for them because I need the time control to update
after
all
the information is added to the record, not before. Any help
would
be
greatly
appreciated.
