Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94

    finding duplicate record in ms access table

    Dear All,

    How do i find duplicate record in ms access table. is there any validation expression that can be used to find the duplicate records or any other method.?

    Please assist.

    rgds,


    aligahk06

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Run the query wizard,FIND DUPLICATES. This works.

    if you are adding records,run a DLOOKUP before you add ,to see if it exists first.

  3. #3
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94
    Can you please explain in details how to run DLOOKUP before adding a record.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    look at the similar threads links at the bottom of your thread - there are several which would appear to meet your requirements

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by aligahk06 View Post
    Can you please explain in details how to run DLOOKUP before adding a record.
    I usually test using DCount() rather than DLookup(). Something like:

    Code:
        If DCount("*", "YourTableName", "txt = '" & Me.ControlNameOnForm & "'") > 0 Then
            MsgBox "Duplicate"
            Cancel = True
        End If
    in the Forms Before Update event
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94
    // For numeric field ( numerical value ) prCol1 table name woPR
    error: error 3075 missing operator
    Any help in this error

    Private Sub prCol1_BeforeUpdate(Cancel As Integer)
    Dim resp As String
    If DCount("prCol1", "woPR", "prCol1 = " & Me.prCol1) > 0 Then
    resp = MsgBox("This Value Already Exists! Do You Wish to Add Anyway?", vbYesNo)
    If resp = vbNo Then
    Cancel = True
    End If
    End If
    End Sub

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Posters often say that something is numeric because to them it is(e.g. 357) but in the table they have a field with Data Type "Short Text" which makes the data stored there text not numeric.
    Are you certain that the field called "prCol1" in table "woPR" is numeric
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94
    Yes,

    prCol1 is defined data type is number.

  9. #9
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Check the value of Me.prCol1 when the code runs
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  10. #10
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94
    Private Sub prCol1_BeforeUpdate(Cancel As Integer)
    Dim resp As String
    If DCount("prCol1", "woPR", "prCol1 = " & Me.prCol1) > 0 Then
    resp = MsgBox("This Value Already Exists! Do You Wish to Add Anyway?", vbYesNo)
    If resp = vbNo Then
    Cancel = True
    End If
    End If
    End Sub

    in Form view while input the existing value 4731 already exist asking message yes or no while clicking no it flash a message
    syntax error Me.prCol1

  11. #11
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    1) Does the control definitely have the name of "prCol1"

    2) What happens if you use:
    Code:
    Private Sub prCol1_BeforeUpdate(Cancel As Integer)
    Dim resp As String
    If DCount("prCol1", "woPR", "prCol1 = " & CInt(Me.[prCol1])) > 0 Then
      resp = MsgBox("This Value Already Exists! Do You Wish to Add Anyway?", vbYesNo)
      If resp = vbNo Then
        Cancel = True
      End If
    End If
    
    End Sub
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  12. #12
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94

    Atached DB ( Validate existing record for prCol1)

    Please see attached DB, Code inserted for control prCol1 to check duplicate but it returns to debug or end while exiting from the form.


    Thanks..
    rgds,
    aligahk06
    Attached Files Attached Files

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    vbno is a number so dim resp as integer

    your use of cInt is unnecessary and will cause an overflow error if the user enters a number outside the integer range.

    I'm guessing your syntax error is due to the field not being populated - i.e. is null

    since the vba opens, the erroring line is highlighted, you can hover over each of the values to see what value is held - then see if your code can handle that error. At the moment you are not handling situations where prCol1 is null, negative or >32677 - but perhaps that doesn't matter for you - you are happy that prCol1 can be populated with these values. But the null situation matters for code and needs to be handled

    Code:
    if isnull(prCol1) then
        'that's OK
    else
        'that's not OK
    end if

  14. #14
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by aligahk06 View Post
    Please see attached DB, Code inserted for control prCol1 to check duplicate but it returns to debug or end while exiting from the form.


    Thanks..
    rgds,
    aligahk06
    The validation seems to be working as it should even without the last addition (CInt(Me.[prCol1])
    However, at the moment, if you enter a duplicate number (e.g. 3368) then the code runs and asks if you really want to use it.
    If you answer "No" then the control is NOT updated but is left with the figure (3368) awaiting update in edit mode (The little pencil signis visible in the record selector).
    If you try to close the form in this state, Access will try to save the record being edited, the Before Update will run again and you will be in this endless loop until you press the "Esc" key to exit the edit of this record.

    Alternatively, a better solution might be to add the following line:

    Code:
    Me.Undo
    immediately after the line:

    Code:
    Cancel = True
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  15. #15
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94
    Sir ,
    Thanks for precious time.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-23-2016, 04:36 PM
  2. Replies: 4
    Last Post: 06-18-2013, 07:36 AM
  3. Finding Duplicate Values
    By TimMoffy in forum Forms
    Replies: 4
    Last Post: 11-21-2012, 10:22 PM
  4. finding last record in a table/form
    By clue74 in forum Access
    Replies: 3
    Last Post: 07-19-2012, 06:46 AM
  5. Replies: 2
    Last Post: 06-20-2010, 06:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums