i am trying to set up a time clock, where my employees need to punch in and out. i set up a weekly calendar for employees to be able to view their time. i also set up a command button to timestamp when they punch in, and punchout. the problem i have is they may punch in and out up to 3 times a day. and i need the command button to enter the information to the first field for the 1st time they punch in, 2nd field on 2nd and so forth. does anyone know how to set this up with only 1 button?

No offense, but I would advise strongly revisiting your database design.

You should not have three separate Punch In fields (and presumably three separate Punch Out fields) on a single row. Each Punch In should be a separate row in a related table.

If you cannot (or will not) correct your design, yes, it is possible to accomodate your setup. You would have to query the table to see whether Punch In 1 is currently null. If it is, you'd put the value in it. If it isn't, you'd check whether Punch In 2 is currently null. If it is, you'd put the value in it. If it isn't, you'd check whether Punch In 3 is currently null. If it is, you'd put the value in it. If it isn't, it's further proof that your design is suboptimal!

Sounds like you have a design issue there. You can't search for nulls because some users may punch in/out fewer than 3 times in a day.

You might want to consider recording the in/out times in a child table, since each user will have many time events, there's a 1:M relationship.

new to access, this is my first design, any suggestions on how to set this up correctly then? i am attaching a copy of my database, let me know the suggestions you have. thanks for the input

The way I have always done this is that each clock in or out (punch) is a separate transaction record.

Table: Time Clock (Punches)
------------------------------------------------
EmployeeID
CreateDate (assuming by the field name this is the date/time that the record was created
Punch Type - (in or Out)
Punch Time (Date and time of the punch ) I would store this data in a single field.

IMHO, this makes the processing a lot easier.

In your command button for creating a punch, I would check to see if there is a previous clock in or out, and then create the appropriate type.

See if this helps:
How to Compare a Field to a Field in a Prior Record


Curious, where did you get the icon used on the Employee form?

Edited by: HiTechCoach on Mon Nov 17 15:34:37 EST 2008.