Prevent duplicate entries on an Input Form

hardhitter06 · Mar 16, 2009

Hi,

I'm using Access 2003.

I have an input form that asks for a Vendor's Fed ID. What I would like to happen is when a user enters a Fed ID that already exists in this Vendor table, something triggers an error message (1) or popup form stating that this number already exists and gives you further options (2).

(2):
-The first button click would allow the user to view the Vendor Information of the Fed ID that they entered which was a match
-The second button just allowing the user to either close out of the input form or go back to the form (one of those).

So I would love to have this set up with the 2nd way but the first would be a big help just so I could prevent duplicates from being entered.

I need some help with this because I'm not exactly sure how to do this without using a combo box for the FedID selection. To explain further, I have done this before when a user enters in an AccountNo (instead of FedID) that is tied to a department so that if they enter in an account that DOESNT exist...a pop up form is triggered allowing them to enter the new acct/dept record. This is a little bit different than what I am doing here because that other database was checking the account numbers through a drop down to see if it DIDN'T exist...while this new database will not be using a combo box and will be checking to see if it DOES exist. This is where I'm confused...I would assume some code would have to go in the FedID's before event but I'm really not sure how you would check in the table to see if this number is already in there?

Please Help if you can..and Thank you

hardhitter06 · Mar 16, 2009

Well, I thought I tried this before but apparently I didn't. I went to the table under FedID field and selected Index -no Duplicates. So now when I try to enter in a FED ID that already exists, at the end of the form it gives me an error.

"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"

This is a slight problem because I don't give my users enough credit when an error like this shows. So I was wondering if one of these solutions was possible:

1:To create a custom error message when this default message is prompted stating something as simple as "This FED ID Already exists..."
2: Or have an error message displayed as soon as I exit the Fed ID box so those users know that's what is generating the error.

rick982 · Mar 20, 2009

Here is the code to trap the standard error message and display a custom one.

Private Sub[YOUR CONTROL NAME]AfterUpdate()

Const conErrRequiredData = 3022
If DataErr = conErrRequiredData Then
MsgBox ("Serial number already exists. Please check your entry and try again.")
Response = acDataErrContinue
Else
'Display a standard error message
Response = acDataErrDisplay
End If
End Sub

Place in the After Update event of the test box and this should do it. It will fire as soon as the user moves to the next block on the form. Obviously you can change the verbage in the MsgBox to whatever you wish.

hardhitter06 · Mar 23, 2009

I put in a new Fed ID to try out this entry and after I did so, I'm being sent to an error in the code where "DataErr" is highlighted and a message box that says "compile error: variable not defined".

How do I define this variable and will I need to define anything else?

hardhitter06 · Mar 24, 2009

Anyone?

TY!

CEH · Mar 24, 2009

Here's one I use on Social Security numbers for a pop up when a duplicate....
SS# is stored in table "tblApplicant"

Code:

Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)

 Dim Answer As Variant
 Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.SocialSecurity.Undo
 
 
 Else:
 End If
End Sub

hardhitter06 · Mar 24, 2009

BINGO...Thanks man. I'll add to your rep.

davidjearly · Jun 5, 2009

Thank you CEH! Worked for me too.

archi85 · Feb 18, 2010

Hi
Thanks CEH.
Your code is working fine that doesn't enter duplicates but is not displaying any error message.
here is my code

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[Day]", "Dailyreport", "[Day] = '" & Me.Day & "'")
If Not IsNull(Answer) Then
MsgBox "Record has already entered in the Database."

Cancel = True
Me.Day.Undo

Else:
End If
End Sub

wakeuky · Jan 22, 2013

Great insight, I was doing the same thing in the afterUpdate property and was getting into the business of saving the record -> counting the number of records -> if greater -> deleteRecord. WHAT A MESS.

I did not realize that beforeUpdate worked like this.

bluenose76 · Feb 8, 2013

CEH,

I used this today and it works like a dream.

Thank you,

Regards,
Arran

CEH said:

Here's one I use on Social Security numbers for a pop up when a duplicate....
SS# is stored in table "tblApplicant"

Code:

Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)

 Dim Answer As Variant
 Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.SocialSecurity.Undo
 
 
 Else:
 End If
End Sub

gemma-the-husky · Feb 9, 2013

just one thing - often, rather than entering an ID at all, a combo box is used to select the ID, by selecting the NAME rather than the ID directly.

by designing a table to prevent users entering duplicate NAES - the problem of duplicate ID's dows not arise

In fact, much of the interface in databases is designed on the premise that users do not need to know ID's at all.

Frank Dz · Mar 1, 2013

CEH said:

Here's one I use on Social Security numbers for a pop up when a duplicate....
SS# is stored in table "tblApplicant"

Code:

Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)
 
 Dim Answer As Variant
 Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.SocialSecurity.Undo
 
 
 Else:
 End If
End Sub

Hello Curtis,

If I am interpreting this code correctly, it does not permit you to edit an existing record. Is there a way to modify this code so that it allows you to edit fields other than SocialSecurity in an existing record?

Frank

IMGR8EST · Mar 8, 2014

Hi, Though I've been tinkering with Access for long, I've never ventured deep into VBA. So when I had an issue of a Date Picker triggering a duplicate error on my field, I tried using DCount and DLookup codes given here but to no avail. Finally it struck me that the field I was interested in has 'Date' data. After changing the "'" sign of text in the DLookup code to "#", the code ran wonderfully! Thanks to everybody here.

tblake · Jun 23, 2015

Hi There,

I have used the below coding below and modified to align with the table and form names I am using, but every time that I go to enter in a company name that I know does not exist in the table it is giving me the error message "Company Name Already Exists. Please Try Again".

Can someone advise me on what I am doing wrong, here is mine after the revisions:

Private Sub Company_Name_as_in_Vistamed_BeforeUpdate(Cancel As Integer)

Dim Answer As Variant
Answer = DLookup("[Company_Name_as_in_Vistamed]", "AMS Renewals and Termination Table", "[Company_Name_as_in_Vistamed] = '" & Me.Company_Name_as_in_Vistamed & "'")
If Not IsNull(Answer) Then
MsgBox "Company Name Already Exists." & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.Company_Name_as_in_Vistamed.Undo

Else:
End If
End Sub
_____________________________________________________________
Your assistance is appreciated.

tblake
___________________________________________________________

CEH said:

Here's one I use on Social Security numbers for a pop up when a duplicate....
SS# is stored in table "tblApplicant"

Code:

Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)

 Dim Answer As Variant
 Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.SocialSecurity.Undo
 
 
 Else:
 End If
End Sub

aftab1965 · Jan 17, 2016

is there any way to prevent duplicate entry into multiple fields

PatAccess · Oct 26, 2017

Hello CEH, I just read this thread and used this helpful code into my database but I was also wondering.
I have 4 field within a table tiltled CWCCompOrder, HWCCompOrder, SFCompOrder, and RFComponentOrder. When a user fills out the form they should only choose a number between 1 and 4. So if they already used 1 for CWCCompOrder, they should not be able to use it again for the other 4 fields within that same record. How do I do that?

Thank you in advance for your help!

CEH said:

Here's one I use on Social Security numbers for a pop up when a duplicate....
SS# is stored in table "tblApplicant"

Code:

Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)

 Dim Answer As Variant
 Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.SocialSecurity.Undo
 
 
 Else:
 End If
End Sub

Uncle Gizmo · Oct 26, 2017

I reckon you'd be better off with a design more like this:-

https://youtu.be/P6H4mXbx9_w

Sent from my SM-G925F using Tapatalk

PatAccess · Oct 26, 2017

Thank for the response. I just tried that
When I opened the index window it has other indexed in there like my PrimaryKey. Does that make a difference?

I have
inxCompOrder as the index Name and all the 4 fields in there. I changed unique from no to yes close the table and came back in. It is still allowing the duplicate. What am I doing wrong? :banghead:

Uncle Gizmo · Oct 26, 2017

PatAccess said:
I have 4 field within a table tiltled CWCCompOrder, HWCCompOrder, SFCompOrder, and RFComponentOrder

You won't be able to use the method described in the video with your current set-up.

You need to put all of the data from the four Fields into a new table called tblComponentOrder. This new table will have three Fields, an ID which will be the same ID it had in the original table.
A field, component ref, for a lookup table that contains CC, CW, SF, RF.... and a field for the 1to 4....

Actually, I'm not sure now, I'm not sure an index can be applied to that new arrangement?

It might be better to use VBA within the form.

Sent from my SM-G925F using Tapatalk

Prevent duplicate entries on an Input Form

Registered User.

Registered User.

New member

Registered User.

Registered User.

Curtis

Registered User.

Registered User.

New member

Registered User.

Registered User.

Super Moderator

New member

New member

New member

Registered User.

Registered User.

Nifty Access Guy

Registered User.

Nifty Access Guy

Similar threads

Users who are viewing this thread