preventing duplicates using vba based on twofields (number/Date)

[mlrucci]

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

[pbaldy]

Date fields need # as a delimiter. There's a multi-field example at the bottom:

http://theaccessweb.com/general/gen0018.htm

[isladogs]

Create a composite index for the two fields so that only unique combinations of both are allowed.
Its a similar idea to primary key fields

If someone tries to enter a duplicate, Access will automatically block it & give an error message.
If you want, you can replace that with your own user friendly message

[mlrucci]

Thank you! That worked. I tried this earlier, but it wouldn't let me as my db was corrupt and I didn't realize it until this afternoon. After fixing the corruption, I was able to make it work. One last question, Can you guide me as to where I might find information to change the message. My db is a bit complex as I am using security and user levels. Because of this, I am using a junction table and am struggling with how to make this happen. Now that I have used the the index in the table, I get the much needed message. I would like to avoid the generic message as it will confuse the end user. Have been working on this for two days and my brain is fried.

[isladogs]

OK - you need to know the error number that is shown and use it below

Then modify your code to include error handling similar to this:

Private Sub txtDateAttendance_AfterUpdate()

On Error GoTo Err_Handler

...rest of your code here minus existing error message

Exit_Handler:
   Exit Sub

Err_Handler:
   If err = N Then <=== replace N with your error number
        MsgBox "This is not allowed as it is a duplicate entry", vbCritical, "Error"
   Else 'standard error message
        MsgBox "Error " & err.Number & " in txtDateAttendance_AfterUpdate procedure" & vbNewLine & err.Description, vbExclamation
   End If
End Sub

[orange]

3022 error

[isladogs]

3022 error

Hi orange
I assume you mean the error number in this case is 3022:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

[orange]

Yes, exactly Colin.
The OP wanted to " prevent a duplicate entry"
and you said

Then modify your code to include error handling similar to this:

and showed some code to capture the error and have a personal/custom message

...Err_Handler:
   If err = N Then <=== replace N with your error number
        MsgBox "This is not allowed as it is a duplicate entry", vbCritical, "Error"
   Else 'standard error message
        MsgBox "Error " & err.Number & " in txtDateAttendance_AfterUpdate procedure" ....

I was just identifying the N that was applicable.
Sorry for any confusion. I don't often have such terse responses.
Usually, I'm on a bit of a soap box with just a few too many words.

[isladogs]

LOL. In my case, usually it's far too many words.