click event condition

[kimangel]

Hi Experts, good day!

I have hard time coding the click event when I click cmdTime on the second time a message box will say, "Your first time in for 2/16/13 at 9:00:33 am, Are you sure you want to record this time in, Yes Cancel?" The time 1:22:33 is the first time in from database. Then the form will unload if yes is click.

Also, when the employee has no time out for the first time in or second time in yesterday because he/she forgot it, when he click timein for current day, a messagebox will say, "You have no (either first or second) time out for 2/17/13. Please input your time out ________(hh:mm:ss). Are you sure you want to record this time out? if yes, the inputted time will record to database. and unload the form.

Help me pls. experts. Thank you very much. below is code.

Private Sub cmdEnter_Click()

Dim sTrID As String

sTrID = txtEmployee_IdNo.Text

Set rs = New ADODB.Recordset
rs.Open "Select * from Employees where Employees_IdNo like '" & sTrID & "'", cn, adOpenKeyset, adLockPessimistic

lblEmployee_Name.Caption = vbNullString

If Not rs.EOF Then

lblEmployee_Name.Caption = rs!Lastname

Ado.RecordSource = "Select * from Employees where Employees_IdNo Like '" & sTrID & "'"
Ado.Refresh

While (rs.EOF = False)

lblEmployee_Name.Caption = rs.Fields(1) & Space(2) & rs.Fields(2) & Space(2) & rs.Fields(3)

rs.MoveNext
Wend

rs.Close
Set rs = Nothing

lblEmployee_Name.Enabled = True
lblHi.Visible = True
cmdTimeIn.Enabled = True
cmdTimeOut.Enabled = False

Else
MsgBox "No records found! ", vbExclamation, "Time In & Time Out"
txtEmployee_IdNo.Text = ""
txtEmployee_IdNo.SetFocus
End If

End Sub

Private Sub cmdTimeIn_Click()

Set rs = New ADODB.Recordset
rs.Open "select*from Time_In", cn, adOpenKeyset, adLockOptimistic

rs.AddNew

rs!Employees_IdNo = txtEmployee_IdNo.Text
rs!Time_In = Format(Now, "hh:mm:ss")
rs!Date_In = Format(Date, "mm/dd/yy")

rs.Update
rs.Close
MsgBox "Your Time In has been recorded! Please come back for your Time Out.", vbInformation, "Time In / Time Out"
Set rs = Nothing
cmdTimeOut.Enabled = True
Exit Sub

End Sub

Private Sub cmdTimeOut_Click()

Set rs = New ADODB.Recordset
rs.Open "select*from Time_Out", cn, adOpenKeyset, adLockOptimistic

rs.AddNew

rs!Employees_IdNo = txtEmployee_IdNo.Text
rs!Time_Out = Format(Now, "hh:mm:ss")
rs!Date_Out = Format(Date, "mm/dd/yy")

rs.Update
rs.Close
MsgBox "Your Time Out has been recorded!", vbInformation, "Time In / Time Out"
Set rs = Nothing

Exit Sub

End Sub

Private Sub Form_Load()
lblEmployee_Name.Enabled = False
lblHi.Visible = False
cmdTimeIn.Enabled = False
cmdTimeOut.Enabled = False

End Sub

Kimangel

[AndreRet]

Your Time_Out must update the already existing record that you created when the user clocked in...

Private Sub cmdTimeOut_Click()
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Time_Out WHERE Employees_IdNo = '" & txtEmployee_IdNo.Text & "'", cn, adOpenKeyset, adLockOptimistic

''rs!Employees_IdNo = txtEmployee_IdNo.Text - Will not need this, already existing...
rs!Time_Out = Format(Now, "hh:mm:ss")
rs!Date_Out = Format(Date, "mm/dd/yy")
rs.Update
rs.Close
MsgBox "Your Time Out has been recorded!", vbInformation, "Time In / Time Out"
Set rs = Nothing
Exit Sub
End Sub

[kimangel]

Thank you sir. sorry for my bad english on above post.

I have hard time coding the click event.

Pls help me code that I would be having an output as situated below;

when I click cmdTime on the second time a message box will say, "Your first time in for 2/16/13 at 9:00:33 am, Are you sure you want to record this time in, Yes Cancel?" The time 1:22:33 is the first time in from database. Then the form will unload if yes is click.

Also, when the employee has no time out for his first time in or on second time in because he/she forgot it, when he click timein for the next day, a messagebox will say, "You have no (either first or second) time out for 2/17/13. Please input your time out ________(hh:mm:ss). Are you sure you want to record this time out?" if yes, the inputted time will be recorded to database. and unload the form.

I don't know to code on the given situation. Help me pls. experts. Thank you very much. below is code.

Private Sub cmdEnter_Click()

Dim sTrID As String

sTrID = txtEmployee_IdNo.Text

Set rs = New ADODB.Recordset
rs.Open "Select * from Employees where Employees_IdNo like '" & sTrID & "'", cn, adOpenKeyset, adLockPessimistic

lblEmployee_Name.Caption = vbNullString

If Not rs.EOF Then

lblEmployee_Name.Caption = rs!Lastname

Ado.RecordSource = "Select * from Employees where Employees_IdNo Like '" & sTrID & "'"
Ado.Refresh

While (rs.EOF = False)

lblEmployee_Name.Caption = rs.Fields(1) & Space(2) & rs.Fields(2) & Space(2) & rs.Fields(3)

rs.MoveNext
Wend

rs.Close
Set rs = Nothing

lblEmployee_Name.Enabled = True
lblHi.Visible = True
cmdTimeIn.Enabled = True
cmdTimeOut.Enabled = False

Else
MsgBox "No records found! ", vbExclamation, "Time In & Time Out"
txtEmployee_IdNo.Text = ""
txtEmployee_IdNo.SetFocus
End If

End Sub

Private Sub Label1_Click()

End Sub

Private Sub cmdTimeIn_Click()

Set rs = New ADODB.Recordset
rs.Open "select*from Time_In", cn, adOpenKeyset, adLockOptimistic

rs.AddNew

rs!Employees_IdNo = txtEmployee_IdNo.Text
rs!TimeIn = Format(Now, "hh:mm:ss")
rs!DateIn = Format(Date, "mm/dd/yy")

rs.Update
rs.Close
MsgBox "Your Time In has been recorded! Please come back for your Time Out.", vbInformation, "Time In / Time Out"
Set rs = Nothing
cmdTimeOut.Enabled = True
Exit Sub

End Sub

Private Sub cmdTimeOut_Click()

Set rs = New ADODB.Recordset
rs.Open "select*from Time_Out", cn, adOpenKeyset, adLockOptimistic

rs.AddNew

rs!Employees_IdNo = txtEmployee_IdNo.Text
rs!TimeOut = Format(Now, "hh:mm:ss")
rs!DateOut = Format(Date, "mm/dd/yy")

rs.Update
rs.Close
MsgBox "Your Time Out has been recorded!", vbInformation, "Time In / Time Out"
Set rs = Nothing

Exit Sub

End Sub

Private Sub Form_Load()
lblEmployee_Name.Enabled = False
lblHi.Visible = False
cmdTimeIn.Enabled = False
cmdTimeOut.Enabled = False

End Sub

[AndreRet]

This is confusing :) Why not just use 1 table with fields
Employees_IdNo
TimeIn
DateIn
TimeOut
DateOut

It will give you much more control over the employee's time attendances. The code I had above will then work perfect - you book an employee in with the time and date by adding a new record. Then, when employee book off, you just search the employee, use his emp. id and then update the same record...

Use your code as normal to add the new record as above... when booking off, use -

Private Sub cmdTimeOut_Click()
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Time_Out WHERE Employees_IdNo = '" & txtEmployee_IdNo.Text & "'", cn, adOpenKeyset, adLockOptimistic

rs!Time_Out = Format(Now, "hh:mm:ss")
rs!Date_Out = Format(Date, "mm/dd/yy")
rs.Update
rs.Close
MsgBox "Your Time Out has been recorded!", vbInformation, "Time In / Time Out"
Set rs = Nothing
Unload Me
End Sub

[kimangel]

thank you sir for this idea, I didnt have an idea to have an update for time out. i'll try to code this one. (:)

[AndreRet]

Only a pleasure. Please mark as solved, thanx

[kimangel]

Hi Sir, I now used 1 table for TimeIn and TimeOut as you suggested, I encounter problem with updated timeOut.

with the following code;

Private Sub cmdTimeOut_Click()

Set rs = New ADODB.Recordset
rs.Open "select * from Time_In where Employees_IdNo ='" + Trim(txtEmployee_IdNo.Text) + "'", cn, adOpenKeyset, adLockPessimistic

txtTimeOut.Text = Format(Now, "hh:mm:ss")
rs!TimeOut = txtTimeOut.Text

rs.Update
rs.Close
Set rs = Nothing
MsgBox "Your Time Out has been recorded!", vbInformation, "Time In / Time Out"
End Sub

My Time_In table has seven fields, two of these are dates e.g. 2/20/13 and 2/21/13. 2/20/13 has TimeIn and TimeOut and 2/21/13 has TimeIn but no TimeOut, when I update TimeOut it will save the time but then right after saving the time it will save to date 2/20/12 as his TimeOut replaces the previous time. Then 2/21/13 TimeOut is empty. Pls help me check the code. Tnx

the form sir has datagrid view.

[AndreRet]

First change this -

rs.Open "select * from Time_In where Employees_IdNo ='" + Trim(txtEmployee_IdNo.Text) + "'", cn, adOpenKeyset, adLockPessimistic

To -

rs.Open "select * from Time_In where Employees_IdNo ='" & Trim(txtEmployee_IdNo.Text) & "'", cn, adOpenStatic, adLockOptimistic ''Added & as well as opening record as static and optimistic...

Then check your data table, what did you specify for the field TimeOut - date, integer, text?

[kimangel]

Sir, I set this txtTimeOut.text in the properties as time with am/pm. I tried to change the sql statement as suggested still the error is the same. When I chose 2/21/13 date line to be updated and click the timeout button the current time will display on the txtTimeOut.text. The time will not save on the date selected in the datagrid view instead it saves on the other date, let say, 2/20/13 date.