when the form is open again it fail to check for existing records,

[princess12]

this section of code prevents duplicates attendance, however when the form REopens again it fails to check if records exists and therefore duplicates the record. also I know due to time it not possible to prevent duplication however I have formatted it to remove the seconds, so hopefully that shouldn't be an issue. it only occurs once the form opens again. it not much of a big deal but my concern is if an employee accidently shuts the application and then attempts to sign in again, it causes duplications.

thanks for your help

heres the code



Private Sub cmdClockIn_Click()
Dim db As dao.Database
Dim rst As dao.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT EmployeeID, ClockID, Status, TheDate, TimeIn FROM tblClockInOut WHERE(TheDate =#" & txtTheDate & "# AND Status='" & cboStatus & "' AND EmployeeID=" & cboEmployee & " AND TimeIn=#" & txtTimeIn & "#" & ")")
If Not rst.EOF Then 'there is a clash
If rst!EmployeeID = cboEmployee And rst!Status = cboStatus And rst!TheDate = txtTheDate And rst!TimeIn = txtTimeIn Then MsgBox "Property Double Booked!"


Else
' add new attendance

[June7]

Formatted where to remove seconds? Is seconds saved with the time data? Setting Format property in textbox does not alter the actual data. The seconds are still there when you reference the textbox in code.

What if the form is reopened 2 hours later? What constitutes duplication?

[princess12]

well the format is saved as short time in table and form
however the textbox of the time in control source it has time(), which has seconds in it.

is it impossible to remove seconds from the time as I need to calculate time at a later stage so that payroll of employee is worked out. this is only possible if the time is calculated. therefore I require attendance to not have duplicates. I tried dlookup and dcount in previous attempts however the date in dcount is accepted but will not prevent duplicate. in previous thread of a different form similar project the expert recommended me not to use dcount or dlookup which is why am trying this attempt. it weird how it works then when it reopens it doesn't.

p.s I put time() to save employees from entering the time and date() for date textbox. I also just removed the time and date and it still the same issue it works in checking records then once it reopens it doesn't work it cause duplicates

thanks for your help

[June7]

Format property in table and textboxes does not alter the data.

I still don't understand what constitutes a duplicate. If form is re-opened 2 hours later and record is created, would that be a duplicate?

[princess12]

well basically it is a duplicate if the record is created with the same data again, it is not a duplicate if a different employee records it attendance. thanks again

[orange]

Princess,

We don't know exactly what your issue is. It is now being further complicated because you are showing/telling us HOW you have done something and things aren't the way you want them.
It is fundamental to communication --we have to understand your issue/opportunity in order to offer any advice/suggestions.
Can you step back and tell us in plain English WHAT you are trying to do? Even better if you can show an example of what you have (input) and what you want as as result (output).
I'm sure you'll get various options once readers understand WHAT you are trying to do.

Good luck with your project.

[princess12]

ok sorry, ill try again

I press the button time in after filling the form in it should record the attendance and if I test it with the same input of data it will give me an error and will not allow the same data to be saved. once I close the form and open it again and type the same data, it will save it when it actually should give me an error and not save it. should I send you my database, sorry if I aint explaining it clear.

[princess12]

second time data entry
Attachment 19704

then when form closes and re opens instead of giving error msg this is what happens
Attachment 19705

[orange]

Plain English--
you have a button that you click and that records the time in. This is all about Attendance

Employees attend something, and you are recording their attendance???

You are telling us HOW you have done something and it isn't working. We're trying to understand WHAT -the "something" is.

[princess12]

oh sorry, I didn't realised or understand that sorry, my mistake

yes it is an attendance system where employees attendance is recorded everytime they enter and exit the building. I am doing this because I want to work out their hours so then I can create my own version of payroll. but first I am trying to fix this issue.

[June7]

I don't think this can be resolved with programming. Seems more like user education issue.

Form is opened, user inputs data, clicks button to save, if user immediately clicks button again another record will not be saved because the data on the form is not changed and would be exact duplicate.

However, form is closed and reopened and new data entered then of course a new record will be created because the data is different.

Although if a duplicate record is created even when there is no date/time data, that is odd.

Yes, I think providing db for analysis is a good idea. Follow instructions at bottom of my post.

[princess12]

thanks for your help

here is the database

[June7]

Records are not duplicated. When form is reopened the TimeIn textbox has a new value. I am not able to get duplicate records unless I change code to not check for time value.

Will this db be run on each user's workstation or only from one computer? Can build database so this form cannot be closed. Can even disable the app X close and File Exit with ribbon customization.

What is really needed is code that makes sure there are not 2 TimeIn or 2 TimeOut records in sequence for the same employee. An In should always be followed by an Out and an Out followed by an In.

Instead of user selecting Status in combobox, have two buttons - ClockIn, ClockOut.

[princess12]

it will only run on one computer. so to clarify your suggestion I should remove cbostatus and have two buttons clock in and out. I understand code needs changing so that an in is followed by an out and vice versa. could you please guide me to the right direction of what part of code do I change or get rid of

thanks a lot for your help

[June7]

Never done anything like this so wild brainstorm.

Have one field for time: TheTime

Have only EmployeeID combobox and two buttons on form.

Should not have both StatusId and Status fields in tblClockInOut. Why does StatusID not show in datasheet?

With this code, form does not need RecordSource.

Private Sub SaveStamp(strStatus)
CurrentDb.Execute "INSERT INTO tblClockInOut(EmployeeID, Status, TheDate, TheTime) " & _
                    "VALUES(" & Me.cboEmployee & ", '" & strStatus & "', Date(), Time())"
End Sub

Private Sub cmdClockIn_Click()
Dim db As dao.Database
Dim rst As dao.Recordset
Set db = CurrentDb()
If Not IsNull(Me.cboEmployee) Then
    Set rst = db.OpenRecordset("SELECT TOP 1 Status FROM tblClockInOut WHERE EmployeeID =" & Me.cboEmployee & " ORDER BY TheDate Desc, TheTime Desc;", dbOpenDynaset)
    If Not rst.EOF Then
        If rst!Status = "ClockIn" Then
            MsgBox "Must click ClockOut"
            Exit Sub
        End If
        Call SaveStamp("ClockIn")
        MsgBox " signed in"
    End If
    Set rst = Nothing
Else
    MsgBox "Must select employee ID"
End If
End Sub

Private Sub cmdClockOut_Click()
Dim db As dao.Database
Dim rst As dao.Recordset
Set db = CurrentDb()
If Not IsNull(Me.cboEmployee) Then
    Set rst = db.OpenRecordset("SELECT TOP 1 Status FROM tblClockInOut WHERE EmployeeID =" & Me.cboEmployee & " ORDER BY TheDate Desc, TheTime Desc;", dbOpenDynaset)
    If Not rst.EOF Then
        If rst!Status = "ClockOut" Then
            MsgBox "Must click ClockIn"
            Exit Sub
        End If
        Call SaveStamp("ClockOut")
        MsgBox " signed out"
    End If
    Set rst = Nothing
Else
    MsgBox "Must select employee ID"
End If
End Sub