Punch IN / Punch OUT Time Clock In Form

[cvansickle]

OK OK !! I surrender! I have been working on this "puzzle for 2 weeks now. I am sure it is an easy solution, BUT I can not figure it out!! I am reasonably new to access, so talk a little slow when replying!



I have a Form (TIMECLOCK) and it is pretty basic.

It has a ComboBox with all employees and a simple password protect.

You select the employee and hit a "CLOCK IN" Button, then it fills in the following

Name
Date
ClockIN

and a few other needed items

All this works find.. But then what I would like...
Is when the employee selects his name again, (same combo box) and hits the "CLOCK OUT" button, It finds the previous record and adds

CLOCKOUT time

I have tried it in the report, I have tried it in the table, I have tried using "SETFIELD", unique fields and "search commands" or "goto commands"

What am I doing wrongs!!

[June7]

The record should be the only one for the employee with ClockOUT Null

CurrentDb.Execute "UPDATE tablename SET ClockOUT = Now() WHERE EmpID=" & Me.tbxEmpID & " AND ClockOUT IS NULL"

However, this will fail if employee forgot to clock out and then does a clock in. So, this could get more complicated to first determine if there are multiple records with ClockOUT Null for the employee, which, actually, should probably be done when they clock in and dealt with at that time.

[cvansickle]

I think I am still doing something wrong (warned you that I am a bit new )

table = TABLEIN
field = END TIME (END_TIME)

I have a field DATENAME that when I clock IN it creates as unique ID (employee's name&date - ex. John Doe3/1/2019)

when they clock out, I have a field HOURS that creates the same unique ID ((employee's name&date - ex. John Doe3/1/2019)

Should the code read

CurrentDb.Execute "UPDATE TableIN SET End_Time = Time() WHERE datename=" & Hours & " AND End_Time IS NULL"

Am I still missing something?

Thanks for all your help!!

[June7]

So when they clock in or out, how do you (and code) know which employee it is?

Names and dates make very poor unique identifiers. Might be unlikely but what if 2 employees have same name?

Use Now() to get full date and time value. Should save full date and time in both fields.

[cvansickle]

I have two Tables

SORTER:
Sorter Name (Employee)
Code (4 digit password)
EmpID (3 digit Unique number)
Master IC (their Manager)

TABLEIN:

Sorter Name (Employee Name)
Start Time
End Time
EmpID (3 digit Unique number)
Master IC (their Manager)
Today
datename (sortername + today() - I have also used (EMPID + today())

So the form has a ComboBox is attached to a Query (SORTER) - that pulls data from Sorter Table

Once the employee selects his name, and hit clock in.. (all data captured in TABLEIN table)

Private Sub Command35_Click()
Me.Text43 = ""


If Me.Combo54 = Me.Text27 Then
Me.Today = Date
Me.Start_Time = Time
Me.EMPID = Forms!timeclock!Combo54.Column(5)
Me.Text45 = "You are Clocked IN"
Me.Text48 = Forms!timeclock!Combo54.Column(1) & " you Clocked IN at " & Me.Start_Time & " on " & Date
Me.Sorter_Name = Forms!timeclock!Combo54.Column(1)
Me.Master_IC = Forms!timeclock!Combo54.Column(2)
[datename] = Forms!timeclock!Combo54.Column(4)
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec


Me.Text27 = ""


Else


Me.Text43 = "INCORRECT PASSWORD"
Me.Text27 = ""
Forms!timeclock!Text27.SetFocus


End If


End Sub

After that the next person can go through the same process..

There is also a Clock Out button.. and this is where my challenge is

[June7]

Should not save SorterName to TABLEIN. Just EmpID. Saving manager info is only needed because managers can change or employee can change positions and you want to know who the manager was at the time. But should save the manager EmpID, not their name.

So, as my suggested code shows, use the EmpID as criteria to retrieve record. This should be possible by referencing combobox.

I don't see any need for the DateName identifier field.

Advise not to use space in object naming convention.

NOTE FOR FUTURE: Please post code within CODE tags to retain indentation and readability.

[cvansickle]

I hope this will be the last time I bother you..

But it is still not working.. I think it is in my code..

You said

CurrentDb.Execute "UPDATE tablename SET ClockOUT = Now() WHERE EmpID=" & Me.tbxEmpID & " AND ClockOUT IS NULL"

I have

CurrentDb.Execute "UPDATE TableIN SET EndTime = Now() WHERE EmpID=" & Tables.TableIN.EmpID & " AND EndTime IS NULL"

the DEBUG highlights the Tables.TableIN.EmpID section.. I have tried it several way..

Again thanks for all the help!! How I would love to have the knowledge

[cvansickle]

Also.. That is the only command I should need in the Clock Out Button. Correct?

[June7]

No, referencing the table in WHERE clause parameter is not possible and really nonsensical. Access has no idea which record you want by that reference. Must reference a field/control on form. If user selects ID/name from combobox, reference the combobox.

And yes, that is all that is required. Although might want to verify the combobox has value first.

If Not IsNull(Me.cbxEmpID) Then

[ssanfu]

UPDATE TableIN SET EndTime = Now() WHERE EmpID=" & Tables.TableIN.EmpID & " AND EndTime IS NULL

The problem is why would you look at TableIN.EmpID??? The table has LOTS of EmpID numbers. You need ONE specific EmpID to update the end time. That is why June7 used Me.tbxEmpID. That control has the EmpID that you want to use to update the end time.

CurrentDb.Execute "UPDATE TableIN SET EndTime = Now() WHERE EmpID = " & Me.tbxEmpID & " AND EndTime IS NULL", dbFailOnError

I just tried it and it updated the EndTime.



Also, you should NOT use Tables.TableIN.EmpID to refer to a table of a field contained in the table.

[cvansickle]

I guess the reason I tried Tables.TableIN.EmpID. is that when I put me.tbxEmpID the DEBUG stopped and highlighted it. So me (not understanding Access as well and you all) thought that maybe June7 meant "x" as "x"= the table I was using.

However, that being said, I tried CurrentDb.Execute "UPDATE TableIN SET EndTime = Time() WHERE EmpID=" & Forms!timeclock!Combo7.Column(4) & " AND EndTime IS NULL"

And thanks to you guys (especially June7) IT WORKED!!!!!

You guys are great.. as I mentioned, I wish I knew even half what you all know. Thanks for the help!!

They only small challenge I have left (and June7 mentioned that I would) is that if they do not clock in and then clock out. It doesnt work. But still, again... Thank you Thank you Thankyou!!

[June7]

I do wonder why EmpID is column 4. Usually the value being saved is the first column (hidden by width of 0") and BoundColumn would be column 1.