Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Checking For Duplicate Information (2000 SR-1)

    I am a VBA novice with a database that has a tblCustomers in which I want to make sure a
    record doesn

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Checking For Duplicate Information (2000 SR-1)

    The recordsetclone is a copy of the form's current recordset, so being on a new record doesn't thwart it unless your form's recordset is filtered down to the new record only. Is there some reason you haven't put a unique key on your table to keep duplicates out?
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking For Duplicate Information (2000 SR-1)

    When I want to do check for duplicates on one or more fields before saving a new record, I sometimes use three forms. It's more work, but gives you more control over what is happening.

    The first form is the "standard" form for editing existing records. Its AllowAdditions property is set to False.

    The second is an unbound "New record" form in which the user can enter the values for a new record. The form has OK and Cancel buttons.

    When the user clicks OK, I perform the usual validation. If something is wrong, I let the user correct it.

    Next, I open the recordset of all existing records filtered by the values on which I want to detect duplicates.

    If this recordset is empty (rst.EOF = True on opening), there are no duplicates. The data are saved (using code).

    If it is not empty, I display the third form, a "Possible duplicates" form. It displays the data of the possible duplicates and asks whether the user still wants to save the new record. This form has Yes, No and Cancel buttons.

    If Yes, the data from the "New record" form are saved (using code) to the table (with a new unique ID).
    The newly created record is displayed in the "standard" form.

    If No, the "New record" form is closed without saving the data entered in it. If desired, the selected "duplicate" is displayed in the "standard" form.

    If Cancel, the user is returned to the "New record" form so that he/she can edit it and try again.

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking For Duplicate Information (2000 SR-1)

    The form has a Add New record button that runs a Macro that has action "GoToRecord." Record is set to New. It appears that this displays only the new record.

    The CustomerID field in the tblCustomer is an autonumber.

    Tom

Posting Permissions

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