Prevent duplicate entries on an Input Form

[hardhitter06]

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]

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]

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]

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]

Anyone?

TY!

 

[CEH]

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

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]

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

 

[davidjearly]

Thank you CEH! Worked for me too.

 

[archi85]

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]

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]

CEH,

I used this today and it works like a dream.

Thank you,

Regards,
Arran

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

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]

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]

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

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]

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]

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
___________________________________________________________

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

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]

is there any way to prevent duplicate entry into multiple fields

 

[PatAccess]

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!

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

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

 

[PatAccess]

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:

 

[PatAccess]

I'm trying out all these options. Will let you know.
Thank you again for your help!

 

[Solo712]

On second thoughts or is it a third thoughts! Yes, the index would work, you'd have to apply it to the ID column and the 1 to 4 column. So I'm being swayed back to doing it the right way so to speak, mainly because in my experience there's always another column added sooner or later!

If you go the VBA route, then that means you've got to rewrite your VBA code, where, going the new table route, you can add as many new components as you like, CC, CW, SF, RF >>>> CX, CZ, CB, etc ... You just need to add them to the lookup table. The only fly in the ointment is you would probably have to add extra values to the 1 to 4 lookup... Still neither way is ideal, it's a choice between the devil and the deep blue sea I reckon....

I liked your "first thoughts" better. Obviously, it is somewhat an exercise in futility to find a solution of a problem if there is a huge design miscue. Even if you succeed in finding a solution in such a situation, you might only be encouraging mischief.

FWIW, (back to the original thread) - for my duplicate checking, I have devised a generalized method in a standard module which I use in the Form's Before_Update event. I don't use before_updates on individual fields because there are issues with such an approach plus having all my form's checks in one place makes designing and troubleshooting much easier.

Public Function IsDuplicated(ffield As String, ddomain As String, Criteria As String) As Boolean
     Dim thisForm As Form, i As Long
     
     Set thisForm = Screen.ActiveForm
     i = DCount("*", ddomain, Criteria)
     If i <> 0 Then
       If thisForm.NewRecord Then
          IsDuplicated = True
       ElseIf thisForm(ffield) <> DLookup(ffield, ddomain, Criteria) Then
           IsDuplicated = True
       End If
    End If
    Set thisForm = Nothing
End Function

You would call it by eg.:

IF IsDuplicated("ID", "MyTable", "Field1=" & Me!Field1 & " AND " & _ 
                "Field2 =" & Me!Field2) Then

where ID has to be a field with a unique value, eg the table's PK. The code tests if Field1 + Field2 are a unique combination. In the function IsDuplicated, two tests are made to find if a match is found. If the record is new then a non-zero DCount means outright there is a duplicate, if it is not new then the ID of the current record must be the same as that of stored recordset which answers to the Criteria.

Best,
Jiri