Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Posts
    25

    Unanswered: Prevent Duplicates w/out keying fields.

    I would like to prevent duplicate names from being entered into a table, but I have an autonumber field as my primary key.

    Fields:
    JP ID: Autonumber (primary key)
    First Name:
    Last Name:
    City:
    Address:


    I would like to be able to enter 2 John Doe's as long as they live at different addresses, but I would like the system to prevent 2 John Doe's with the same address from being entered into the system. I could do this by ditching the autonumber field and keying the name and address fields together, but I'm sure there is a better way than that.

    Any advice?
    Jeff Perry

  2. #2
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    I don't see a way you can do this in your table definition. You can off course use VB code in your forms when adding a new record.

    somthing like

    public sub AddNewRecord ()
    dim rstTest as new ADO.Recordset
    dim strSQL as string

    strSQL = "SELECT * FROM tablename WHERE " & _
    "First Name = ' " & me.textboxFirstName & _
    " ' AND Last Name = ' " & me.textboxLastName & _
    " ' AND City = ' " & me.textboxCity & _
    " ' AND Address = ' " & me.textboxAddress & " ' "

    rstTest.Open strSQL,currentproject.connection,vbopenkeyset,adlo ckoptimistic

    if not rstTest.EOF then
    rstTest.close
    exit sub
    end if

    rstTest.Close

    'write here the procedure to add

    end sub

    You might also want to try out the before insert event on your forms

    anna
    Last edited by annavp; 04-04-02 at 04:43.

  3. #3
    Join Date
    Apr 2002
    Posts
    25

    TY

    Thanks a ton. I'm relatively new at VB so I'll give something like that a try.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    How about . . .

    in the BeforeUpdate event procedure of the form which is doing this file maintenance, use the following code:

    Code:
    If DCount("*","tblCustomers","[FirstName]=me![FirstName] AND [LastName]=me![LastName] AND [Address]=me![Address] AND [City]=me![City])<>0 then
         MsgeBox "Sorry, this customer already exists!"
         Cancel =True
    EndIf
    Ideally, you want to index all four of these fields.

    Remember, whichever of these fields are string fields, you are going to have to do the following to the criteria:

    instead of [FirstName]=me![FirstName]

    it will have to be [FirstName]='" & me![FirstName] & "'

    that's [FirstName]=single-quotedouble-quotespaceampersandspaceme![FirstName]spaceampersandspacedouble-quotesingle-quote

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •