How to prevent entering duplicate value

[MNNovice]

My Table “Cast” has these fields:

cID (PK), cFName, cMName and cLName

The form I am using to update this table is called fCast.

I would like to prevent entering same name twice. To prevent entering same title twice I am using codes which is working. But I am not sure how to concatenate 3 fields and apply similar codes. Can someone please help? Thanks.

My code to prevent duplicating a title:

Private Sub iTitle_AfterUpdate()



Dim iTitle As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    iTitle = Me.iTitle.Value
    stLinkCriteria = "[iTitle]=" & "'" & Replace(Me![iTitle], "'", "''") & "'"
    
     
    
    'Check Items table for duplicate iTitle
    If DCount("iTitle", "Items", _
              stLinkCriteria) > 0 Then

              
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning Item Title " _
             & iTitle & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Title
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
    
End Sub

[ADezii]

My Table “Cast” has these fields:

cID (PK), cFName, cMName and cLName

The form I am using to update this table is called fCast.

I would like to prevent entering same name twice. To prevent entering same title twice I am using codes which is working. But I am not sure how to concatenate 3 fields and apply similar codes. Can someone please help? Thanks.

My code to prevent duplicating a title:

Private Sub iTitle_AfterUpdate()



Dim iTitle As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    iTitle = Me.iTitle.Value
    stLinkCriteria = "[iTitle]=" & "'" & Replace(Me![iTitle], "'", "''") & "'"
    
     
    
    'Check Items table for duplicate iTitle
    If DCount("iTitle", "Items", _
              stLinkCriteria) > 0 Then

              
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning Item Title " _
             & iTitle & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Title
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
    
End Sub

The easiest Method of making sure that no 2 Names are exact, namely the combination of First Name, MI, and Last Name is to create a Unique Index (No Duplicates allowed) consisting of these 3 Fields. If you are not sure how to do this, just let us know.

[MNNovice]

The easiest Method of making sure that no 2 Names are exact, namely the combination of First Name, MI, and Last Name is to create a Unique Index (No Duplicates allowed) consisting of these 3 Fields. If you are not sure how to do this, just let us know.

I do not know how to do this unique indexing. Please let me know. Thanks.

[ADezii]

I do not know how to do this unique indexing. Please let me know. Thanks.

1. Open the Cast Table in Design View.
2. Click on View ==> Indexes.
3. In the 1st open Row under the Index Name Column, enter FullName.
4. In the same Row, select cFName under the Field Name Column.
5. In the same Row, set the Sort Order for this Field.
6. Without leaving this Row, under Index Properties set:
   • Primary = Yes
   • Unique = Yes
   • Ignore Nulls = No
7. Drop down to the next Row, select cMName for Field Name, and again set the Sort Order.
8. Drop down to the next Row and select cLName for Field Name, again set the Sort Order.
9. Exit the Dialog.

Now, Alfred E Newmann will not be allowed to be entered into these Fields twice, but Alfred A Newmann and Alfred Newmann will.

[MNNovice]

1. Open the Cast Table in Design View.
2. Click on View ==> Indexes.
3. In the 1st open Row under the Index Name Column, enter FullName.
4. In the same Row, select cFName under the Field Name Column.
5. In the same Row, set the Sort Order for this Field.
6. Without leaving this Row, under Index Properties set:
   • Primary = Yes
   • Unique = Yes
   • Ignore Nulls = No
7. Drop down to the next Row, select cMName for Field Name, and again set the Sort Order.
8. Drop down to the next Row and select cLName for Field Name, again set the Sort Order.
9. Exit the Dialog.

Now, Alfred E Newmann will not be allowed to be entered into these Fields twice, but Alfred A Newmann and Alfred Newmann will.

Primary Key for Cast table was originally set to be cID - I had to delete the relationship of this table to make the changes you suggested. Now I am facing another problem.

1. How do I restablish the relationship so all other tables, forms and reports are not affected?
2. What, if any, choices I have in the event there are two persons with the same name?

Thanks.

[ADezii]

Primary Key for Cast table was originally set to be cID - I had to delete the relationship of this table to make the changes you suggested. Now I am facing another problem.

1. How do I restablish the relationship so all other tables, forms and reports are not affected?
2. What, if any, choices I have in the event there are two persons with the same name?

Thanks.

My mistake in that you should set the Primary Key Property of the Index to No (Item #6 - Step #1), while setting the Unique Property = Yes. You could then re-establish the Original Relationships based on the [cID] Field.

[MNNovice]

My mistake in that you should set the Primary Key Property of the Index to No (Item #6 - Step #1), while setting the Unique Property = Yes. You could then re-establish the Original Relationships based on the [cID] Field.

I changed my tables indexes to look like this. Still it's allowing duplicates of names like Richard Gere. What am I doing wrong?

Rows  Index Name  Field Name  Sort Order
1     FullName    cFName      Ascending
2                 cMName      Ascending
3                 cLName      Ascending
4     ID          cID         Ascending

  Primary  No  
  Unique  Yes  applies to rows 1 - 3
  Ignore Nulls  No

[ADezii]

I changed my tables indexes to look like this. Still it's allowing duplicates of names like Richard Gere. What am I doing wrong?


Rows Index Name Field Name Sort Order
1 FullName cFName Ascending
2 cMName Ascending
3 cLName Ascending
4 ID cID Ascending

Primary No
Unique Yes applies to rows 1 - 3
Ignore Nulls No

The problem, as I see it, has to do with how the Index is comparing NULLS in the cMName Field within the Index. It does not equate two NULLs which makes sense since a NULL cannot be equal to anything else, even another NULL. The way I see is is that you have two Options:

1. Create a Unique Index on the First and Last Name Fields which would not make sense since Richard Gere and Richard A Gere would be considered a Duplicate which they very well may not be.
2. Check the Values in the First, MI, and Last Name Fields for Duplication in the BeforeUpdate() Event of a Data Entry Form. This is more complex, but more efficient.

[OldBirdman]

If this were my project, I would not allow cMName to be null. By setting the initial value to "", the string is zero length, but is not null.
If these names are entertainers, as the example Richard Gere suggests, then cFName and cLName should be treated the same way, as some entertainers use only their first (Cher) or their last (Liberace) name.
Now is also the time to consider whether names such as Sir Laurence Olivier or Douglas Fairbanks, Jr. are going to be entered into the database, and how you will handle them.

[MNNovice]

If this were my project, I would not allow cMName to be null. By setting the initial value to "", the string is zero length, but is not null.
If these names are entertainers, as the example Richard Gere suggests, then cFName and cLName should be treated the same way, as some entertainers use only their first (Cher) or their last (Liberace) name.
Now is also the time to consider whether names such as Sir Laurence Olivier or Douglas Fairbanks, Jr. are going to be entered into the database, and how you will handle them.

OldBirdman,

Thanks for your comments. But you didn't say what I should be doing. Is it worth the trouble of breaking up names like this? Or just keep it as "cName" to indicate full name of an actor. Because like you said there are too many variables to consider, (only first name, only last name....)

I wait for your comments. Thanks.

[OldBirdman]

"... I would not allow cMName to be null. By setting the initial value to "", the string is zero length, but is not null." This is a specific suggestion to eliminate null values in cMName. I suggest you not use null to mean a zero-length string. Testing/comparing to a null value is different than testing/comparing strings or numeric values. This is true in VBA and SQL. The simple/easy solution is to not let them happen, and Access table field properties make it easy and automatic.
"... cFName and cLName should be treated the same way..." This too is a specific suggestion. Once you change the properties of cMName, do the same for cFName and cLName also. This makes all name fields the same, so programming errors will be less likely in the future.
I don't know how you will handle honorifics(pref ixes) and/or suffixes. I am a believer in design BEFORE implimentation. My examples were only to expand on the names, and have you consider what you needed to consider uniqueness.
When working with table fields derived from 'natural' language, many problems must be considered.
1) People's names - a few things to consider:
a) Sir Laurence Olivier wasn't always Sir Laurence.
b) Some people use their full middle name, not just an initial. Peggy Sue is not Peggy S.
c) Some people have multiple middle names - J.R.R.Tolken
d) Some people only use a first initial.
e) The suffix "Jr." may distinguish between two individuals
f) Two actors may have the same name. This is usually not a problem unless one later becomes a star. Then the old films may be revived.
2) Movie/Music/Book titles
a) Duplicates often occur
b) Normally the articles (English) "A", "An", and "The" are ignored in sorting and listing
c) Sorting of numbers within names can result in different sort order.
d) No standard spelling. English examples Grey/Gray or Theater/Theatre.
e) Numbers replace words Second/2nd or Twelve/12.
Does this answer your questions. No, because I cannot. When you decide what to store, and how, then the queries can be addressed. It may be that the best solution is to Filter the data, and let the user select from a small subset of the table. Or maybe not.

[Marknut]

Back to your original question and coding technique, why not try something like this:

Private Function Check_Dups() 
    
Dim iNames As String 
    Dim stLinkCriteria As String 
    Dim rsc As DAO.Recordset 
  
    Set rsc = Me.RecordsetClone 
  
    iNames = Me.cFName.Value & " " & Me.cMName.Value & " " & Me.cLName.Value
    stLinkCriteria = "[cFName] like Forms!frmForm1![cFName] AND [cMName] like Forms!frmForm1![cMName] AND [cLName] like Forms!frmForm1![cLName]")
  
  
  
    'Check Items table for duplicate Names
    If DCount("cNameID", "tblNames", _ 
              stLinkCriteria) > 0 Then 
  
  
        'Undo duplicate entry 
        Me.Undo 
        'Message box warning of duplication 
        MsgBox "Warning Item Name " _ 
             & iNames & " has already been entered." _ 
             & vbCr & vbCr & "You will now been taken to the record.", _ 
               vbInformation, "Duplicate Information" 
        'Go to record of original Title 
        rsc.FindFirst stLinkCriteria 
        Me.Bookmark = rsc.Bookmark 
    End If 
  
    Set rsc = Nothing 
  
End Function

[MNNovice]

My Table “Cast” has these fields:

cID (PK), cFName, cMName and cLName

The form I am using to update this table is called fCast.

I would like to prevent entering same name twice. To prevent entering same title twice I am using codes which is working. But I am not sure how to concatenate 3 fields and apply similar codes. Can someone please help? Thanks.

My code to prevent duplicating a title:

Private Sub iTitle_AfterUpdate()



Dim iTitle As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    iTitle = Me.iTitle.Value
    stLinkCriteria = "[iTitle]=" & "'" & Replace(Me![iTitle], "'", "''") & "'"
    
     
    
    'Check Items table for duplicate iTitle
    If DCount("iTitle", "Items", _
              stLinkCriteria) > 0 Then

              
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning Item Title " _
             & iTitle & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Title
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
    
End Sub

MarkNut:

Thanks for your suggestion. I tried the following code and added it to Modules (Global Code). It didn't like the Me. command. So I added to sfCastOther (which is the actual sub form I am using to add / delete names). However, the code didn't stop entering duplicate names.

Please suggest. Thanks.

Private Function Check_Dups()
Dim FullName As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
  
    Set rsc = Me.RecordsetClone
  
    FullName = Me.cFName.Value & " " & Me.cMName.Value & " " & Me.cLName.Value
    stLinkCriteria = "[cFName] like Forms!sfCastOther![cFName] AND [cMName] like Forms!sfCastOther![cMName] AND [cLName] like Forms!sfCastOther![cLName]"
  
  
  
    'Check Items table for duplicate Names
    If DCount("cID", "Cast", _
              stLinkCriteria) > 0 Then
  
  
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning Item Name " _
             & FullName & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Title
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

End Function

[ADezii]

The following Code, placed in the BeforeUpdate() Event should handle 'most' contingencies, just substitute you own Field Names and Text Box Names. The only problem with this Code is that Andrew A Fuller and Andrew A. Fuller would NOT be considered Duplicates because of the period after the MI in the second case.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

strCriteria = "[Firstname] = '" & Me![FirstName] & "' AND Nz([MI]) = '" & _
               Nz(Me![MI]) & "' AND [LastName] = '" & Me![LastName] & "'"

If DCount("*", "Employees", strCriteria) > 0 Then
  MsgBox Me![FirstName] & " " & IIf(Len(Me![MI]) = 0, "", Me![MI] & " ") & _
         Me![LastName] & " already exists!", _
           vbExclamation, "Duplicate Entry on Name"
             Cancel = True
End If
End Sub

[Marknut]

Right on ADezii, that should do it.

MNNovice, I've attached a very basic example for your reference in case you have any issues.