I have a form where the employee enters a date. On the after update event I want to check to see if they put that date already in a table to prevent duplicate entries.

Table: tblTimeClockData
table fieldnames: EmployeeID, DateIn

Form: txtEmployeeName, txtDateIn

Any ideas how I can do this?

Simplest method would be to create a unique index in your table based on those two fields. In your tables Design View, click on Indexes, then input a name such as uqEmployeeDate and add the EmployeeID and DateIn fields.

If you always needed a date filled in for each employee, you could also make the DateIn field part of your Primary Key.

Hi petite39,

For data validations, it's better to use the BeforeUpdate event so that you can "stop" the user from entering the wrong information.

For your setup, you could also use the DCount() function to check for duplicate records. For example:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblTimeClockData", "EmployeeID=" & Me.txtEmployeeID & " And DateIn = #" & Me.txtDateIn & "#") > 0 Then
Cancel = True
MsgBox "Duplicate record! Please try again."
End If
End Sub

Notice that I used "txtEmployeeID" because I am not sure what txtEmployeeName contains. The above code assumes a numeric data for the employee ID fiedl.

Just my 2 cents...

Thanks to both of you. I will give these a try.

Hi,




Darnell and I are happy to help. Good luck with your project.

Ok I'm in design mode of the table. Found the thing for indexes.

The primary key is recid which is an autonumber field.
The next index is employeeid and named the same.

I added uqEmployeeDate. I put datein as the fieldname. But how do I add another field? On a seperate line under it or add a comma???

separate line underneath - nothing in the Index Name Column, only in the Field Name column. Make sure you select Unique = Yes on the Index Name.