Prevent duplicate entries in linked table

[arothacker]

Hello-



I have a form (Add New Headcount) that inputs to a linked table. There is a field called ID that I need to prevent duplicates in. When the user opens the form and types in an ID, I want it to prevent them from submitting the new data if the ID already exists in the linked table.

Normally you could go into the design view of the table and change the Index to prevent duplicates but because the table is linked this is no longer an option.

Any help is appreciated! I've been struggling with this for quite some time now! Thanks.

[Missinglinq]

Many of us dislike the system warnings for this kind of thing and 'roll our own,' using DCount():

Replacing Field1 with the actual name of your Field/Control, and Domain with the name of the Table or Query that holds the Field:

If Field1 is Text

Private Sub Field1_BeforeUpdate(Cancel As Integer)
 If DCount("Field1", "Domain", "[Field1] = '" & Me.Field1 & "'") > 0 Then
  Resp = MsgBox("This Value Already Exists! Please Enter New Value")
  Cancel = True
 End If
End Sub

If Field1 is Numeric

Private Sub Field1_BeforeUpdate(Cancel As Integer)
 If DCount("Field1", "Domain", "Field1 = " & Me.Field1) > 0 Then
  Resp = MsgBox("This Value Already Exists! Please Enter New Value")
  Cancel = True
 End If
End Sub

Linq ;0)>

[arothacker]

Thanks for this! I've tried doing DCount and for some reason it still doesn't work. The field name in the table "Updated Headcount" that I do not want duplicates in is called WWID. The text box in the form that populates that field is called "txtWWID". I clicked on the txtWWID in the form and put the code in the BeforeUpdate event. When I typed in a WWID that already existed nothing happened. Any idea what I'm doing wrong??

Private Sub txtWWID_BeforeUpdate(Cancel As Integer)
If DCount("WWID", "Updated Headcount", "[WWID] = '" & Me.WWID & "'") > 0 Then
Resp = MsgBox("This Value Already Exists! Please Enter New Value")
Cancel = True
End If
End Sub

[arothacker]

Figured out the problem. Macros were disabled and there was no popup notifying it. In the trust center settings I enabled macros and now this code works perfectly. Thanks!!

[Missinglinq]

Glad you got it working!

Good luck with your project!

Linq ;0)>

[arothacker]

I'm actually looking back into this because we have another criteria to add.

I have the code:

Private Sub txtWWID_BeforeUpdate(Cancel As Integer)
If DCount("WWID", "Updated Headcount", "[WWID] = '" & Me.txtWWID & "'") > 0 Then
MsgBox ("This Value Already Exists! Please Enter New Value")
Cancel = True
End If
End Sub


What I need is if the value exists in the WWID column AND there is a 1 in the Number column then the message box pops up. Any ideas??