langianom
12-23-2015, 09:34 AM
Hello,
I am using Access 2010 and have constructed a fairly simple database with a few tables and forms. I want to prevent duplicate entries in a field. I have Googled the problem and got some good code samples that works too good. It is actually blocking all entries that are entered into the field and I cannot figure out why. Here is the outline of the Database.
Table is named ComputerFields
Column in the table I want to prevent duplicate entries is named Asset Tag
TextBox Name is Asset Tag
I know that I can set the Primary Key to be this field and it will automatically prevent duplicate entries, but I need to prevent duplicate entries on more than one field and one of those fields is already set as the primary key.
Approach #1,
I created an index on the Asset Tag column and set the index properties to Yes, no duplicates. This does the trick, but the error message it displays is non-descriptive to what field(s) is the duplicate and it is not very clean.
Approach #2,
I tried the below code and it works, but it literally blocks everything that is entered into the field no matter if it is exists in the table or not.
Private Sub Asset_Tag_AfterUpdate()Dim NewAssetTag As String
Dim stLinkCriteria As String
NewAssetTag = Me.Asset_Tag.Value
stLinkCriteria = "[Asset_Tag] = " & "'" & NewAssetTag & "'"
If Me.Asset_Tag = DLookup("(Asset_Tag)", "ComputerFields", stLinkCriteria) Then
MsgBox "This Asset Tag, " & NewAssetTag & ", already exists.", vbInformation, "Duplicate Asset Tag"
Me.Undo
End If
End Sub
Approach #3,
Is the same code and outcome of Approach #2, but I did it under the Private Sub Asset_Tag_Before Update(Cancel As Integer).
Approach #4,
Was different code, but still treats all text as a duplicate, I think I figured out though that this code was for a Number, not a Text Box.
Private Sub Asset_Tag_BeforeUpdate(Cancel As Integer)
If DCount("Asset_Tag", "ComputerFields", "Asset_Tag=" & Me.Asset_Tag) > 0 Then
MsgBox "Asset Tag Already Exists."
Cancel = True
End If
End Sub
Can anyone tell me what I am doing wrong to have the code blocking all entries instead of just duplicates?
Thank you in advance,
Matt
I am using Access 2010 and have constructed a fairly simple database with a few tables and forms. I want to prevent duplicate entries in a field. I have Googled the problem and got some good code samples that works too good. It is actually blocking all entries that are entered into the field and I cannot figure out why. Here is the outline of the Database.
Table is named ComputerFields
Column in the table I want to prevent duplicate entries is named Asset Tag
TextBox Name is Asset Tag
I know that I can set the Primary Key to be this field and it will automatically prevent duplicate entries, but I need to prevent duplicate entries on more than one field and one of those fields is already set as the primary key.
Approach #1,
I created an index on the Asset Tag column and set the index properties to Yes, no duplicates. This does the trick, but the error message it displays is non-descriptive to what field(s) is the duplicate and it is not very clean.
Approach #2,
I tried the below code and it works, but it literally blocks everything that is entered into the field no matter if it is exists in the table or not.
Private Sub Asset_Tag_AfterUpdate()Dim NewAssetTag As String
Dim stLinkCriteria As String
NewAssetTag = Me.Asset_Tag.Value
stLinkCriteria = "[Asset_Tag] = " & "'" & NewAssetTag & "'"
If Me.Asset_Tag = DLookup("(Asset_Tag)", "ComputerFields", stLinkCriteria) Then
MsgBox "This Asset Tag, " & NewAssetTag & ", already exists.", vbInformation, "Duplicate Asset Tag"
Me.Undo
End If
End Sub
Approach #3,
Is the same code and outcome of Approach #2, but I did it under the Private Sub Asset_Tag_Before Update(Cancel As Integer).
Approach #4,
Was different code, but still treats all text as a duplicate, I think I figured out though that this code was for a Number, not a Text Box.
Private Sub Asset_Tag_BeforeUpdate(Cancel As Integer)
If DCount("Asset_Tag", "ComputerFields", "Asset_Tag=" & Me.Asset_Tag) > 0 Then
MsgBox "Asset Tag Already Exists."
Cancel = True
End If
End Sub
Can anyone tell me what I am doing wrong to have the code blocking all entries instead of just duplicates?
Thank you in advance,
Matt