I have a db to track employee attendance. tblLogTA has fields TALogID (autoNumber), DateAttendance (Date/Time), EmployeeID (Number). I want to prevent a duplicate entry for the same EmployeeID and DateAttendance. EmployeeID is RI to tblEmployee. I have tried multiple vba codes and am stuck on trying to get it work. I am new to vba coding and am self taught. I have the following 2 codes (not working) to try and start the process. example (1) is for just the EmployeeID, and (2) is another version for both. Can anyone help?
Example 1: EmployeeID only
Private Sub txtDateAttendance_AfterUpdate()
Dim EmployeeID As Integer
Dim DateAttendance As Date
Dim StLinkCriteria As String
EmployeeID = Me.EmployeeID.Value
DateAttendance = Me.DateAttendance.Value
StLinkCriteria = "[EmployeeID] = " & " '" & EmployeeID & "'"
If Me.EmployeeID = DLookup("[EmployeeID]", "tblLogTA", StLinkCritera) Then
MsgBox " This event has already been created in the database." & vbCr & vbCr & "This entry will be removed.", vbInformation, "Duplicate Entry"
Me.Undo
End If
End Sub
Example 2: tired both criteria
Private Sub txtDateAttendance_BeforeUpdate()
'If CheckDuplicates(Me!EmployeeID, Me.DateAttendance) Then
If DCount("[TALogID]" = "tblLogTA", "[DateAttendance]='" & Me!DateAttendance & "' And [EmployeeID]='" & Me!EmployeeID & "'") > 0 Then
msg = "There has already been an entry for this Employee and Date" & vbNewLine
msg = msg & "This record will be undone"
MsgBox msg, vbExclamation, "System Duplication Message"
Me.Undo
Cancel = True
End If
End Sub