Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Question Unanswered: Duplicate Values in a form

    greets;

    (im on access 2003) and i want a form to search and for duplicates values when a new record is entered and display a msg if someone already exsists.

    However, as people can have the same surname/first name and DOB i want it to check all three fields and see iof they all correlate with the one that has been inputted and display a msg box saying a duplicate has been found, and either a) continue anyway, or b) discard the record.

    i found this thread; http://www.dbforums.com/showthread.php?t=759025 but not sure how to implement it to check the 3 fields on both the form and in the main table.

    Thanks in advance;

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's a handy reference on domain aggregate functions, including multiple criteria:

    http://www.mvps.org/access/general/gen0018.htm
    Paul

  3. #3
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Thanks for the reply, though i have no idea how to implement it in my database.

    The three field i want my form to compare are:

    Surname, Firstname, DateofBirth

    I want it to return an error message If all 3 match the one being typed into the form, before it is commited to the table and say

    "Duplicate Entry Exists" or something similar;

    and;

    if 2 out of three match have it return;

    "Similar record found", with the option to be able to go ahead and commit the record anyway.

    I'd also like for it to be able to display the record(s) that match.

    thanks again all,

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Dan,

    I can't tell you the "best" or "easiest" way, I can only tell you the way I'd do it if I was in your shoes.

    Upon entering the third of the three parameters - less assume it's DOB - have the following in the AfterUpdate event:

    Private Sub DOB_AfterUpdate()

    Dim Rst As DAO.Recordset, Msg1 As Integer

    Set Rst=CurrentDb.OpenRecordset("SELECT * FROM YourTableName " _
    & "WHERE YourTableName.Surname = '" & Me.Surname & "' And " _ & "YourTableName.FirstName = '" & Me.FirstName & "' & YourTableName.DOB " _ & "= #" & Me.DOB & "#;",dbOpenDynaset)
    If Rst.RecordCount > 0 Then
    Msg1=MsgBox("This Person Already Exists. Do You Want to Add Anyway",vbYesNo)
    If Msg1 = vbYes Then
    Do something
    Else
    Do something else
    End If
    End If
    End Sub
    I've made a number of assumptions here, but you get the idea.

    Hope this helps,

    Sam

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Sam Landy
    Dan,

    I can't tell you the "best" or "easiest" way, I can only tell you the way I'd do it if I was in your shoes.

    Upon entering the third of the three parameters - less assume it's DOB - have the following in the AfterUpdate event:

    Private Sub DOB_AfterUpdate()

    Dim Rst As DAO.Recordset, Msg1 As Integer

    Set Rst=CurrentDb.OpenRecordset("SELECT * FROM YourTableName " _
    & "WHERE YourTableName.Surname = '" & Me.Surname & "' And " _ & "YourTableName.FirstName = '" & Me.FirstName & "' & YourTableName.DOB " _ & "= #" & Me.DOB & "#;",dbOpenDynaset)
    If Rst.RecordCount > 0 Then
    Msg1=MsgBox("This Person Already Exists. Do You Want to Add Anyway",vbYesNo)
    If Msg1 = vbYes Then
    Do something
    Else
    Do something else
    End If
    End If
    End Sub
    I've made a number of assumptions here, but you get the idea.

    Hope this helps,

    Sam
    Oops! Change that SQL slightly, as follows:
    "SELECT * FROM YourTableName " _
    & "WHERE YourTableName.Surname = '" & Me.Surname & "' And " _ & "YourTableName.FirstName = '" & Me.FirstName & "' And YourTableName.DOB " _ & "= #" & Me.DOB & "#;"

  6. #6
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Talking

    Thanks Sam;

    Worked a treat, sorted the bits i had to and now all is sweet and good! ;0)

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

Posting Permissions

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