Re: Null field when checking for duplicates in an Index

Discussion in 'Access Table Design' started by John W. Vinson, Jan 18, 2011.

  1. One way (and it's not ideal!) is to set the field's Allow Zero Length property
    to Yes, permitting a text string "" to be entered. If you do so, set its
    Default Value to "" and make it Required.

    A zero-length-string is still a string, and (unlike a NULL) it can be indexed
    and made part of a Primary Key.

    Do note that it would simply be *wrong* to force uniqueness on this
    combination of fields! I once worked with Dr. Lawrence David Wise and his
    colleague Dr. Lawrence David Wise, both US citizens. Names simply are not
    unique, and you can't base your database design on the assumption that they
    are.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Jan 18, 2011
    #1
    1. Advertisements

  2. One good check is to use the data entry Form's BeforeUpdate event to check for
    duplicates. Either use DLookUp or open a Recordset to find existing names in a
    different record; if you find one, don't prohibit entry, but warn the user of
    the duplication and give them the choice to jump to the found record, add the
    new record anyway, or just cancel.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Jan 18, 2011
    #2
    1. Advertisements

  3. Well, the critical issue is whether there is another person with the same
    name; I don't think you really care if there are one, or three, or seventeen!
    DCount() makes the computer work harder than DLookUp(), because it must
    retrieve and count all the hits; DLookUp() can stop after it finds one.

    Try:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim iAns As Integer
    'Check for duplicate first and last name using DLookUp
    If Not IsNull( DLookUp("[Sur]", "[tblDrivers]", _
    "[Sur]= " & Chr(34) & Me![Sur] & Chr(34) & _
    " And [Fore] = " & Chr(34) & Me![Fore] & Chr(34)) Then
    Beep
    Ians = MsgBox("This Name already exists in the database. " _
    & "Please check that you are not entering a duplicate Driver " _
    & " before continuing. Click Yes to add anyway, No to quit.", _
    & vbYesNo, "Duplicate Value")
    If iAns = vbNo Then
    Cancel = True
    ' if the user clicks Yes, don't do anything, leave Cancel false
    End If
    End Sub

    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Jan 20, 2011
    #3
  4. Simply use a Combo Box to select from the existing set of drivers. Display the
    driver name and nationality, and any other information that would uniquely
    identify him or her. The combo will autocomplete, and it has a Not In List
    feature which will let you write code to pop up the data entry form for
    drivers.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Jan 23, 2011
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.