Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate Records (Access 97)

    (Edited by HansV to provide clickable links to posts - see <!help=19>Help 19<!/help>)

    Hans V had posted a response to a user's question (long ago) about checking for duplicate information. I have a database that will have a list of employees undergoing training. I want to create a record for new employees and all of their needed courses to qualify, (if interested, see post <post#=366322>post 366322</post#> again, thanks, Hans). If, however, that employee exists, I want to follow the same process Hans outlined in his <post#=148162>post 148162</post#>

    Thanks in advance for all your help.

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

    Re: Duplicate Records (Access 97)

    To elaborate a bit on <post#=148162>post 148162</post#>, I would not allow the user to add a new record in the form used to display and edit the existing employee records. This can be accomplished by setting the AllowAdditions property of this form to No.

    To add a new record, I would use an unbound form, i.e. a form whose Record Source property is blank. Put controls (text boxes, combo boxes, check boxes) on this form to enter all the fields the user needs to fill in. Since the form is unbound, the Control Source of these controls is blank. An easy way to create such a form is to create a form bound to the employees table using one of the form wizards, then clear the control source of the text boxes etc. and the record source of the form. This form should also contain an OK button and a Cancel button. The Cancel button only closes the form. The OK button does the following:
    - Opens a DAO recordset on the employees table.
    - Performs a FindFirst with a criteria string based on the fields you want to check for duplicates on (see below.)
    - If one or more duplicates are found, either warns the user, or opens another form that displays the potential duplicates.
    - Depending on the response of the user, does or does not save the new employee record.

    Here is an incomplete example. If you would like more detailed help, you'll have to provide more specific information about your situation. In this version, the user is prompted what to do if a duplicate on a field named SSN is detected. The code could be expanded and refined, but this is the basic idea:

    Private Sub cmdOK_Click()
    Dim rst As DAO.Recordset
    Dim strSearch As String
    Dim volgnr As Long
    Dim i As Integer

    On Error GoTo ErrHandler

    ' Test input
    If IsNull(Me.txtSSN) Then
    MsgBox "Please enter a Social Security Number.", vbExclamation
    Me.txtSSN.SetFocus
    Exit Sub
    End If

    ' Check for duplicate SSN
    strSearch = "SSN=" & Chr(34) & Me.txtSSN & Chr(34)
    Set rst = CurrentDb.OpenRecordset("tblEmployees", dbOpenDynaset)
    rst.FindFirst strSearch
    If rst.NoMatch = False Then
    ' Found a duplicate
    If MsgBox("There is already an employee with Social Security Number " & Me.txtSSN & vbCrLf & _
    "Do you want to add the new record anyway?", vbYesNo + vbQuestion) = vbNo Then
    GoTo ExitHandler
    End If
    End If

    ' Save data
    With rst
    .AddNew
    !SSN = Me.txtSSN
    If Not IsNull(Me.txtFirstName) Then !FirstName = Me.txtFirstName
    If Not IsNull(Me.txtLastName) Then !LastName = Me.txtLastName
    .Update
    End With

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Records (Access 97)

    Thanks, Hans. I'm going to test it now. I will post back with questions. You the MAN!

  4. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Records (Access 97)

    A couple of questions about the last post.

    1) How do I show the duplicates in the recordset in a new form so they can decide?
    2) How do I stop Access from saving a record if a user closes a form?
    3) What are the variables "volgnr" and "i" for?

    Thanks for everything.

    Steve05

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

    Re: Duplicate Records (Access 97)

    1) In the code I posted, a string strSearch is assembled. This can also do duty as WhereCondition argument for a DoCmd.OpenForm instruction. Create a form based on the employees table (or query), and open it like this:

    DoCmd.OpenForm FormName:="frmDuplicateEmployees", WhereCondition:=strSearch, WindowMode:=acDialog

    This form has command buttons "Save new record anyway" and "Cancel new record". Clicking either button sets a global variable and closes the form. The "new employee" form picks up after that, inspects the global variable to decide whether the data in the form should be saved or not.

    2) Preventing Access from saving a record is tricky - usually, you want the record to be saved automatically... That's why I proposed an unbound form for entering a new record.

    3) I adapted the code in my previous reply from an existing database and I simply forgot to remove those declarations. They have no function in the code as posted.

  6. #6
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Records (Access 97)

    thanks, Hans. This will save me lots of time.

Posting Permissions

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