Clocking in (and out), and how can I do it?

[anubislg]

Hello!

I'm rather new to VBA, and Access in whole, so please bear with me.
I'm currently trying to create a form that will automatically fill in
the current time when a user inputs data into a specific text box. In
short, I wish to make a form that when an employee enters there
employee number, it will log the current time.
Now, I've got it to where when I input the employee number into the
text box, the time field will automatically update with the current
time...but the number one main problem I've ran into, is when you
re-open the form, the time updates to the current time that you opened
the form, not the first time of clock-in!

Current code that I'm using is:
In the Control Source of the "Start Time";
=IIf(IsNull([Clock In]),Null,IIf(IsNull([Main]![Topaz Start
Time]),Now([Main]![Topaz Start Time]),[Main]![Topaz Start Time])

Now, this code (at least to me) should work. In essance, it should
check to see if [Clock In] is null, if it is, then enter no
information, if it isn't null though, then check to see if the record
[Main]![Topaz Start Time] is null, if it is, then fill in the record
with the current time, if the record isn't null, then return the
record. But it seams that I've gone astray somewhere with this
formula. Could anyone offer me some hints? Thank you all!

-Matthew

 

[John Vinson]

Current code that I'm using is:
In the Control Source of the "Start Time";
=IIf(IsNull([Clock In]),Null,IIf(IsNull([Main]![Topaz Start
Time]),Now([Main]![Topaz Start Time]),[Main]![Topaz Start Time])

Now, this code (at least to me) should work. In essance, it should
check to see if [Clock In] is null, if it is, then enter no
information, if it isn't null though, then check to see if the record
[Main]![Topaz Start Time] is null, if it is, then fill in the record
with the current time, if the record isn't null, then return the
record. But it seams that I've gone astray somewhere with this
formula. Could anyone offer me some hints? Thank you all!

-Matthew

For one thing, it's far more complex than it need be. You can use the
NZ() function instead. For another, the Now() function returns the
system clock time - it doesn't take any arguments (you're trying to
pass it the form reference). For yet another, you don't need to - and
shouldn't - use the form name in this way; if the code is on form Main
you need only use the control name, or you can use Me![Topaz Start
Time], or you can use [Forms]![Main]![Topaz Start Time] - but not just
[Main]!...

Finally, this code will DISPLAY a time value, once it gets corrected -
but it will not cause that value to be stored in any table field. The
Control Source is a calculated expression, not bound to any table
field.

So: what *are* the relevant fields in your Table, and to which form
controls are those fields bound (as Control SOurce)?

John W. Vinson[MVP]

 

[AnubisLG]

First of all, thank you John for your help! As I said before, I'm
still quite the novice at Access. I suppose I should have been a bit
more definative about what I was calling out in my scrap heap of random
code!

=IIf(IsNull([Clock In]),Null,IIf(IsNull([Main]![Topaz Start
Time]),Now([Main]![Topaz Start Time]),[Main]![Topaz Start Time])

"[Main]![Topaz Start Time]" Is supposed to be the reference to the
table (Main) and field (Topaz Start Time). I had just taken a wild
stab that I could manipulate the data in the table via this method.
"Clock In" is the name of the form control for which I wish to display
the time inputed to the table, and is also the control for which I
wrote the original code for (and inserted into the control source).

So I suppose my questions now are;
1. Can I maniupulate table data using a form control's control source,
and if not, then how could I go about doing it? Did I just use
incorrect syntax for calling to the table?
2. I'll definatly have to do more research into the NZ() function you
told me about.

Thank you again for your aid!
-Matthew

 

[AnubisLG]

Correction:
"Clock In" is the control which a user enters there employee number.
The code I wrote was ~not~ placed into "Clock In", but rather a control
called "Start Time." Just wanted to clarify that. Burning midnight
oil is taking it's tole.
-Matthew