Problem with my time clock database

[Shawn Yates]

I am fairly new to MS Access and VB but thanks to many of the posts on
this site I have found solutions to my problems. I have been searching
all day, however, for an answer to my current problem but I haven't
found anything so I have joined and I hope someone can help me.

I have a form with the field [EmployeeName] and many buttons. Currently
one button "Clock IN" opens another form in add mode then autopopulates
the [EmployeeName] field with the same name on the previous form.

On this new form there are three fields:
1) Date
2) TimeIN
3) TimeOUT

There is also a button "Clock In." When Clock in is pressed the date
and TimeIN fields are populated with the current date and time, and the
button now displays "Clock Out." If the employee pushes the button
again it will fill in the TimeOUT field. Every time this is done it
creates a new record in the table "Time" includin Name, Date, TimeIN,
and TimeOUT.

The problem is that after the employee clocks in they will close the
form and then when they come back in to clock out they will have to
reopen the form. Since the form is in add mode it just creates a new
record instead of going back to their previous record where the TimeOUT
field is Null.

Is there a way that the button on the first form will:
1) Open the second form using the current Employee's name
2) Check to see if THIER last entry has a null value in the TimeOUT
field
3) If it does then open that record and if it doesn't add new record
using current Employee name

I hope this all made sense
Thanks,
Shawn Yates

[pietlinden@hotmail.com]

Re: Problem with my time clock database


Sounds like you need to do a search for records for that employee for
that day where the ClockOut time is null.

SELECT ...
FROM TimeClock
WHERE EmployeeID = Forms!MyTimeClo ckForm!cboEmplo yeeID
AND ClockInDate = Forms!MyTimeClo ckForm!txtClock InDate
AND ClockOutDate IS NULL...

If that record exists, update it, if not, create a new date. In
Oracle, you can use NOTEXISTS, but can't do that in Access. You'd have
to try to open a recordset based on the query, and do a recordcount.
If you get one, then update the clockout value for the record,
otherwise, create a new one and update the clock in info. In Access,
you have to use VB to do this.

[tina]

Re: Problem with my time clock database

if all employees clock in and out on the same day (no 3rd shift), suggest
the following:
1) change the 2nd form from Add mode to Edit mode (or "normal", whatever).
2) change the form's RecordSource to a query based on the same table that
currently backs the form. put criteria in the [EmployeeName] field to refer
back to the 1st form, as

[Forms]![FirstFormsName]![EmployeeName]

also put criteria in the date field (if you've really named the field
"Date", recommend you change it since Date is a reserved word in Access), as

Date()

so if there is a record in the table for that employee with today's date,
then that record will populate the form. otherwise, the form will be blank,
and you can add a new record.

hth


"Shawn Yates" <syates@cc.usu. eduwrote in message
news:1151816424 .480587.33720@m 73g2000cwd.goog legroups.com...

I am fairly new to MS Access and VB but thanks to many of the posts on
this site I have found solutions to my problems. I have been searching
all day, however, for an answer to my current problem but I haven't
found anything so I have joined and I hope someone can help me.
>
I have a form with the field [EmployeeName] and many buttons. Currently
one button "Clock IN" opens another form in add mode then autopopulates
the [EmployeeName] field with the same name on the previous form.
>
On this new form there are three fields:
1) Date
2) TimeIN
3) TimeOUT
>
There is also a button "Clock In." When Clock in is pressed the date
and TimeIN fields are populated with the current date and time, and the
button now displays "Clock Out." If the employee pushes the button
again it will fill in the TimeOUT field. Every time this is done it
creates a new record in the table "Time" includin Name, Date, TimeIN,
and TimeOUT.
>
The problem is that after the employee clocks in they will close the
form and then when they come back in to clock out they will have to
reopen the form. Since the form is in add mode it just creates a new
record instead of going back to their previous record where the TimeOUT
field is Null.
>
Is there a way that the button on the first form will:
1) Open the second form using the current Employee's name
2) Check to see if THIER last entry has a null value in the TimeOUT
field
3) If it does then open that record and if it doesn't add new record
using current Employee name
>
I hope this all made sense
Thanks,
Shawn Yates
>

[Shawn Yates]

Re: Problem with my time clock database


Thanks that was a perfect solution to my problem. I was over
complicating it thanks for the help.
Shawn Yates

[tina]

Re: Problem with my time clock database

you're welcome :)


"Shawn Yates" <syates@cc.usu. eduwrote in message
news:1151952031 .435656.19610@7 5g2000cwc.googl egroups.com...

>
Thanks that was a perfect solution to my problem. I was over
complicating it thanks for the help.
Shawn Yates
>