Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a table with and ID field that is is the primary key field. I have FN and LN fields in the table.

    I have a form to add new names to the table. The form has FN and LN fields and a field to combine the fields into LNFN. What code can I use to make a message popup and tell the user that that name already exists in the table and ask the user if (Yes or No) he/she still wants to add the name?

    Thanks.

    Paul

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Have a look at the attachment. The code runs in the Before Update event of both the name fields.

    [attachment=87323:CheckDuplicates.zip]
    Attached Files Attached Files
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    Have a look at the attachment. The code runs in the Before Update event of both the name fields.

    [attachment=87323:CheckDuplicates.zip]

    Thanks, works great!

    Now could you help me go one step further. Where in the code would I add "Me.FirstName.Setfocus" so that after you say no, the focus would go back to the Firstname field? I have tried several locations in the code but I can't get it to work.

    Paul

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can't change the focus in the Before Update event of controls. If you want to do that, you must remove the Before Update event procedures for the FirstName and LastName text boxes, and add a Before Update event procedure for the form as a whole instead:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      Dim lngCount As Long
      If Me.NewRecord And Not IsNull(Me.LastName) And Not IsNull(Me.Firstname) Then
        lngCount = DCount("*", "tblPeople", "Firstname=" & Chr(34) & Me.Firstname & _
          Chr(34) & " And LastName=" & Chr(34) & Me.LastName & Chr(34))
        If lngCount > 0 Then
          If MsgBox("A person with the same names already is present in the db." & _
              vbNewLine & "Do you still want to add the new name?", vbYesNo, _
              "Duplicate person?") = vbNo Then
            Cancel = True
            Me.Firstname.SetFocus
          End If
        End If
      End If
    End Sub
    Note: this version does not undo the record but gives the user the chance to correct the name. See the attached version.

    [attachment=87325:CheckDuplicates.zip]
    Attached Files Attached Files

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by HansV View Post
    Note: this version does not undo the record but gives the user the chance to correct the name.
    Whether to Undo or not is a choice. I took the view that the user had already decided they did not want to create the new record, so it would probably help to remove it.

    The problem I find in this situation is that the user does not have enough information to make the decision.
    How do you know whether this John Smith is the same as the one already there, or not?

    So typically, I enter the firstname and lastname into unbound controls. The system then checks for duplicates and displays a list of matches, providing (hopefully) enough info to allow a decision. If none of the matches look the same, you can still create the new record.
    Regards
    John



  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by John Hutchison View Post
    So typically, I enter the firstname and lastname into unbound controls. The system then checks for duplicates and displays a list of matches, providing (hopefully) enough info to allow a decision. If none of the matches look the same, you can still create the new record.
    That's the solution I used in several databases - it's much better than letting the user guess.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I decided that I liked John's version better and I experimented a little more and seem to have gotten his version to work closer to what I wanted.

    I basically added close and open form commands after the undo code. That removed the cursor from the Last Name field. I alos set the form to add new records only.

    See attached.

    Thanks to both of you for your help.

    Paul
    Attached Files Attached Files

Posting Permissions

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