employee Timestamp in/out

[pommie78]

I am struggling to get my head around how to do this.

I have 2 tables in a database. 1. EmpT. (EmpID, EmpName) 2. EmpTimeT (EmpTimeID, EmpID, TimeIn, TimeOut).
I have a list on the form which shows all EmpNames from EmpT and that automatically pulls the EmpID into a hidden field.
I have set up a button to create new record in EmpTimeT with TimeIN = now() and the EmpID of the person highlighted in the list, but can't figure out how I can get the timeOut as well.

I tried using 1 button to do time in, which created a new record in EmpTimeT with EmpID and TimeIn, and a TimeOut button to do TimeOut = now() when pressed, but the timeout button ended up creating a new record with TimeOut stamped instead of updating the TimeOut field in the record that was created with the TimeIn button.

Ideally I would have only 1 button and if a person is selected and they have not already clocked in, it would, when they click it, create a new record of the EmpID and the TimeIn. Then if they click it again, it would time stamp the TimeOut against that record. So it would always be creating a new record unless they had not clocked out, in which case it would stamp the TimeOut field.



If 1 button setup is difficult, then I could settle for a timeout button as well to update the timeout field.

I guess I'm struggling to comprehend how to do an always create new record unless timeIn is not null and timeOut is null, in which case update timeout.

I thought excel was difficult but I can get by on that ok, access is a different and new beast that is getting the better of me.

Any ideas would be really appreciated.
Thanks.

[pbaldy]

One button is feasible. Open a recordset on an SQL statement that finds records for the chosen person where the time out is Null. If it finds a record, edit the record and set the time out field. If it doesn't, create a new record.

[pommie78]

Hi pbaldy. Thanks for the advice and response.,

I've spent probably too long trying to work this out today with no joy. I just don't know how to write it to get the SQL statement to search for the null records of the selected person highlighted in my list 'lstEmployeeID'. The below is what I managed to throw together from never having heard of a recordset before you mentioned it.

Private Sub Command15_Click()
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("EmployeeTimeT")
With rs.Filter = "lstEmployeeID"
If TimeOut Is Null Then
With rs
.Edit
.Fields("TimeOut") = Now()
.Update
End With
Else
With rs
.AddNew
.Fields("EmployeeID") = txtEmployeeID
.Fields("TimeIn") = Now()
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

I'll have another crack at this tomorrow but unfortunately it's all a bit beyond me at the moment and it definitely doesn't work and is probably a long way off, but thanks again.

[pbaldy]

You mention an SQL statement but don't actually use one. More like

Set rs = db.OpenRecordset("SELECT * FROM EmployeeTimeT WHERE TimeOut Is Null And EmployeeID =" & Me.txtEmployeeID)

then test the recordset for EOF. True means it didn't find a record.

[pommie78]

Hi Paul.

Thanks for your help and instructions and apologies for the delayed reply which was due to me not having a clue how to apply the great advice you were providing, so I spent some time getting to know ms access a little bit, but more importantly the birth of my daughter. She sort of took the focus off this little project for a few weeks.

Anyway I finally got it to work thanks to your help. I did rs and rss because I wanted to count the records via debug.print to make sure there were some along the way. I know it's all a bit (lionel) messi but its a first real crack at it. Had error 3464 because my EmployeeID fields in the table were not the same. One was a number and one was text. Schoolboy error.

Private Sub Command34_Click()


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rss As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM EmployeeTimeT WHERE TimeOut Is Null AND EmployeeTimeT.EmployeeID =" & me.EmployeeID


Set db = CurrentDb
Set rs = db.OpenRecordset("EmployeeTimeT")
Debug.Print rs.RecordCount


Set rss = db.OpenRecordset(strSQL)
If Not rss.BOF And Not rss.EOF Then
rss.MoveFirst
rss.MoveLast
With rss
.Edit
.Fields("TimeOut") = Now()
.Update
End With


Else
MsgBox "no records, create new"
With rss
.AddNew
.Fields("EmployeeID") = Me.EmployeeID.Value
.Fields("TimeIn") = Now()
.Update
End With



End If
Debug.Print rss.RecordCount


rs.Close
rss.Close
Set rss = Nothing
Set rs = Nothing
Set db = Nothing
End Sub


Thanks again.

[pbaldy]

Happy to help and congrats from the proud father of two great daughters!