Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate entry (office 2000)

    Hello,
    I have an employee database where I would like to stop further entry if the Social Security number is the same as another. I have changed the field properties in the table for "Indexed" to "Yes (No Duplicates)". This works ok, but it doesn't tell you that there is a duplicate until you try to either save the record or go to the next record. The error message that pops up is vague as to what the problem is.

    I would like a small window to pop up stating that the Social Security number is a duplicate. Is there an easy way to do this?

    Thanks,
    Louise

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

    Re: Duplicate entry (office 2000)

    You could use the On Error event of the form:

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
    Case 3022
    ' Duplicate index - show custom message
    MsgBox "The SSN " & Me.SSN & " that you entered already exists." & _
    vbCrLf & "Please enter another SSN or cancel this record.", vbCritical
    ' Select the SSN
    Me.SSN.SetFocus
    ' And suppress the standard error message
    Response = acDataErrContinue
    Case Else
    ' Other error - display standard error message
    Response = acDataErrDisplay
    End Select
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry (office 2000)

    Hi Hans,

    Thank you very much. I went to design view of the form and I couldn't see "On Error". Am I looking in the right place?
    Louise

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

    Re: Duplicate entry (office 2000)

    Open the form in design view.
    Make sure that the title bar of the Properties window says Form.
    Activate the Event tab of the Properties window.
    Scroll down until you see On Error, it's between Key Preview and On Filter.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry (office 2000)

    Hi Hans,
    Ah! I see it now. Thanks for the direction. I was looking in the properties of the field SOCSEC.

    I copied and pasted your procedure but it didn't work for me. I assumed that it was because of the "SSN"? so I changed all of the "SSN" in the event procedure to it's control source and name of "SOCSEC" but still nothing. What do you think I am doing wrong?

    Thanks again,
    Louise

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

    Re: Duplicate entry (office 2000)

    I should have mentioned that SSN was to be replaced by the name of your SSN field/control. Sorry about the omission.

    Open the form in design view again.
    Make sure that the title bar of the Properties window says Form.
    Click in the On Error event on the Event tab of the Properties window.
    Select [Event Procedure] from the dropdown list in this event.
    Click the ... button to the right of the dropdown arrow.
    This will activate the Visual Basic Editor, and the insertion point should be in the procedure you copied and adapted.
    If not, you should copy the code (without the lines Sub ... and End Sub) into the new procedure that has been created, and substitute the correct name.
    Switch back to Access, save the form and see if it works now.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry (office 2000)

    Hi Hans,

    Yes, it works now thanks!

    I see what the problem was though. I had created a couple of larger command buttons to go to the next / previous record and to save the record. Those for some reason did not work with the new onError procedure. But if I use the standard Access next record arrow at the bottom of the screen or the design view button or save button, it worked. Strange.

    Thanks again!
    Louise

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry (office 2000)

    Hans,

    If I wanted to include those Command buttons (command215, command216, command251) so that they would also work, would I then change the event procedure as well?

    Thanks,
    Louise

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

    Re: Duplicate entry (office 2000)

    Another option is to check for duplicates in the Before Update event of the SOCSEC text box:

    Private Sub SOCSEC_BeforeUpdate(Cancel As Integer)
    Dim n As Integer
    n = DCount("*", "NameOfTable", "SOCSEC=" & Chr(34) & Me.SOCSEC & Chr(34))
    If n > 0 Then
    MsgBox "This SSN already exists! Please enter a different one.", vbCritical
    Cancel = True
    End If
    End Sub

    This will check the SSN when the user exits the text box.

    Note: the above assumes that SOCSEC is a text field. If it is a number field, use

    ... "SOCSEC=" & Me.SOCSEC)

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

    Re: Duplicate entry (office 2000)

    Sorry, I don't understand why the command buttons would interfere with the On Error code, but see my other reply, it suggests an alternative approach.

  11. #11
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate entry (office 2000)

    Hi Hans!

    Perfect! Thank you very much! You're brilliant! What would I or this forum do without you!
    Louise

Posting Permissions

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