Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Checking for duplicate records during input (Access 2k)

    Could anyone assist me with the following problem:

    I have a user input form that allows an employee to input among other fields, ClaimNo, (number) and UnitNo (number.) The table that is the record source for the form also contains an ID field which is a replication autonumber field and is set as the primary key.

    While not a common occurrence, users have input records with the same ClaimNo and UnitNo, creating duplicate records (as they usually input the same or similar information in the numerous other fields on the form.

    I would like to be able to create an afterupdate event that would check these fields to make sure that they have not already been entered into the database. The field ClaimNo can have duplicates, but ClaimNo and UnitNo in conjunction with each other should be unique. I currently have a query that checks for duplicates and identifies the records with duplicate ClaimNo and UnitNo entries. I then however must manually check each one and delete the one I do not want to remain in the database.

    My other alternative is to create a query that will at the click of a button (or other event) run a delete query that would delete the original record as I have a field that logs the date each record is created and updated along with the windows ID of the user that created or updated the record. I would think this is not the preferred method to accomplish this so I am seeking assistance.

    Thank you in advance for any assistance.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking for duplicate records during input (Access 2k)

    First method :
    Create an compound index on the field ClaimNo and UnitNo. Set the unique property of this index to yes.
    No programming and access will give an error if the duplicated entries are made.

    Second method:
    In the before update of the form do a test with dcount :
    <pre>Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Dcount("*","MyTable","ClaimNo = " & Me.ClaimNo & " And UnitNo = " & Me.UnitNo) > 0 Then
    MsgBox "You are entering duplicated info"
    Cancel = True
    End If
    End Sub</pre>

    This suppose ClaimNo and UnitNo are numeric fields.
    If the are text fields use :
    <pre>If Dcount("*","MyTable","ClaimNo = '" & Me.ClaimNo & "' And UnitNo = '" & Me.UnitNo & "'") > 0 Then</pre>

    Replace Mytable by your table name and eventually Me.ClaimNo and Me.UnitNo by the control names in your form.
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking for duplicate records during input (Access 2k)

    Thank you for the assistance. I will likely work with the second option first, as I want to alert the user prior to their inputting the remainder of the fields.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking for duplicate records during input (Access 2k)

    Then you'll have to put the code in the afterupdate event of one of the controls ClaimNo or UnitNo and add code to check if the other control has some input.
    Francois

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking for duplicate records during input (Access 2k)

    Ok, I see now that the original code is for the form's before update event. I have an understanding of this now, with the exception of the DCount function. I see that you provided code for both text and numeric entries. However, the first field is text (some of these entries must be alpha numeric) and the second is numeric only. I am having trouble integrating these two in the string. I will likely use the code in the after update event of the next field, as the UnitNo field has a default value, so this field is sometimes skipped unless it is necessary to change it from the default of 1.

    Thanks again for your assistance.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking for duplicate records during input (Access 2k)

    You have not to put the two in a string. Use :
    <pre>If Dcount("*","MyTable","ClaimNo = '" & Me.ClaimNo & "' And UnitNo = " & Me.UnitNo) > 0 Then</pre>

    Be carefull if you set it in the after update of ClaimNo only. In this case it will always look for a ClaimNo and a UnitNo as 1. If they change the UnitNo after they input the claim, you will not have a check with the right UnitNo. You could set the code in the two after updates. In the case the user don't change the UnitNo, the check will use the default 1. If they change the UnitNo, a second check will occur with the right UnitNo.
    DCount function :
    Count all records ("*") in a table or query ("Mytable") where certain condition is true or fals (ClaimNo = the ClaimNo on the form(Me) And UnitNo = the UnitNo on the form(Me)
    In your case, if the count returns a number greater then 0 the the record already exist. If it return 0 the record don't exist
    Francois

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking for duplicate records during input (Access 2k)

    Thank you Francois. It works like a charm.

Posting Permissions

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