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]

@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.

[MNNovice]

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

[ADezii]

@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.

[MNNovice]

@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.

[ADezii]

@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.

[MNNovice]

@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

[ADezii]

@MNNovice
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]

@OldBirdman
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(prefixes) 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]

@MNNovice
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.

[MNNovice]

@ADezii
AdeZii:

Thanks for your tips. I modified the code and added to the sub form. It works ONLY where the name has all 3 parts (first, middle and last). If someone has only first name - it allows duplicates.

Any modification to the codes? Here is what I used. Thanks.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String
 
strCriteria = "[cFName] = '" & Me![cFName] & "' AND Nz([cMName]) = '" & _
               Nz(Me![cMName]) & "' AND [cLName] = '" & Me![cLName] & "'"
 
If DCount("*", "Cast", strCriteria) > 0 Then
  MsgBox Me![cFName] & " " & IIf(Len(Me![cMName]) = 0, "", Me![cMName] & " ") & _
         Me![cLName] & " already exists!", _
           vbExclamation, "Duplicate Entry on Cast "
             Cancel = True
End If
 
End Sub

[MNNovice]

@Marknut
Marknut:

Thanks again. Perhaps I should have explained in greater details about the table structures.

sfCastOther is based on "Cast". It's one of several subforms that is residing on a form called fOther. I use fOther to add delete many other data like category, language etc.

fOther is based on a query called qOtherList which has a table called OtherList. This table has two fields and no primary key: olCaption and olObject. Both these fields use text data type.

Now on to your little DB sample. I understand how it works. But how do I incorporate into my form is where I am stuck.

Let me ask you this, can I use the code behind the command button on cLName Before Update event? If not, what are my choices?

Thanks.

[MNNovice]

@ADezii
ADzeii,

The codes working only when all three parts of a name exist. It does not work when there is only one name (cFName), like Cher. Can you direct me how to modify these codes to include only firstname and/or only last name?

Thanks.

[ADezii]

@MNNovice
You could check for every possible contingency, but it would significantly increase the amount of code. The easiest approach would be to make the First Name Required, while the MI and Last Names are not. Is this doable on your end?

[NeoPa]

I'm sorry. I'm coming to this late, but has the option of setting up a Compound Index covering these three fields been discussed yet? It seems to me that this would be a viable solution. Clearly Nulls would have to be allowed in the individual composite fields, but as long as all three were not Null it should be able to include the values in the index and duplication would automatically be precluded.

[MNNovice]

@ADezii
Yes, requiring first name is possible. I made this change in the cFName's property making Required to Yes. What else do I need to do?

[MNNovice]

@NeoPa
NeoPa:

Please see Posting #7. Is this what you referred to as Compound Indexing? It didn't work. Thanks.

[ADezii]

@NeoPa
This was my advice in Post #4, maybe I missed something.

[Marknut]

@MNNovice
Haha, sorry, the answers are everywhere here because you definitely have multiple options. You should be able to use that code wherever you want. This doesn't seem like it should be too hard to implement in your db. How large is your database? Is it confidential information or anything? If you zip it and post it I'd be glad to make changes, note them down, and repost. I'm sure ADezii or NeoPa would be glad to do the same.

[ADezii]

@MNNovice

Dim strCriteria As String
Dim strCriteria_2 As String
 
strCriteria = "[Firstname] = '" & Me![FirstName] & "' AND Nz([MI]) = '" & _
               Nz(Me![MI]) & "' AND [LastName] = '" & Me![LastName] & "'"
 
If IsNull(Me![LastName]) Then
  strCriteria_2 = "[Firstname] = '" & Me![FirstName] & "' AND Nz([MI]) = '" & Nz(Me![MI]) & "'"
    If DCount("*", "Employees", strCriteria_2) > 0 Then
      MsgBox Me![FirstName] & " " & IIf(Len(Me![MI]) = 0, "", Me![MI]) & _
             " already exists!", _
               vbExclamation, "Duplicate Entry on Name"
                 Cancel = True
    End If
Else
  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 If

[MNNovice]

@ADezii
ADzeii:

Thank you very much. It worked perfectly. Much appreciated. M

[ADezii]

@MNNovice
You are quite welcome.

[NeoPa]

MNNovice: Please see Posting #7. Is this what you referred to as Compound Indexing? It didn't work. Thanks.

This is pretty well what I meant yes. It would be interesting to know in what way it failed. This would certainly be the area I would explore more deeply before any other. I appreciate that may be a little more difficult for you than us of course. If you attach a database as ADezii suggested, then please explain also in what way this is not working for you then I can explore for myself a little and see if I can see why this (most sensible approach) is not working in your project.

[MNNovice]

@NeoPa
NeoPa / ADezii

It's working perfectly so far. Shall let you know if I encounter any problems. Thanks again.