Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate Number Warning (2000)

    I have a form that enters new customer information into a table. One of the fields is the customerID, which is a "No Duplicates" field. Currently, when the user fills out the form and they use a

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Number Warning (2000)

    I use this code in before update to warn about duplicate serial numbers:

    Private Sub SERIAL_BeforeUpdate(Cancel As Integer)
    Const qt = """" 'four quotes
    If DCount("[SERIAL]", "EQUIPMENT SCHEDULE", "[SERIAL] = " & qt & Me.SERIAL & qt) > 0 Then
    MsgBox ("This is a Duplicate Serial Number")
    Exit Sub
    End If
    End Sub

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

    Re: Duplicate Number Warning (2000)

    I don't think Exit Sub does anything here, I would use Cancel = True instead, but I'm not sure this will help.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Number Warning (2000)

    Forgive me but is "EQUIPMENT SCHEDULE" the table the Serial number is coming from or is that something else?

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Location
    Griffith (Near Chicago), Indiana, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Number Warning (2000)

    Have you set the index property in the table design for the index to be yes with no duplicates? This will bring up a duplicate error message.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Number Warning (2000)

    Yes I did, but it doesn't bring up any error messages at all. It just closes to the form (because the button coded to do that) and all the information is gone. It doesn't overwrite the other information that corresponds to that ID. If it isn't a dup. ID, it enters the information in the table without a problem.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Number Warning (2000)

    Using this code:

    Private Sub iMISNumber_BeforeUpdate(Cancel As Integer)

    Const qt = """" 'four quotes
    If DCount("[IMISNumber]", "tblAttendees", "[IMISNumber] = " & qt & Me.iMISNumber & qt) > 0 Then

    MsgBox ("This is a Duplicate Serial Number")
    Exit Sub

    End If

    End Sub

    I get this error

    Run-Time Error 3464
    Data type mismatch in criteria expression

  8. #8
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Number Warning (2000)

    Taken from Access help searching under 'Error 3022' (no I can't remember all of the error codes - this one happens to be fresh in my mind!)


    The following example shows how you can replace a default error message with a custom error message. When Microsoft Access returns an error message indicating it has found a duplicate key (error code 3022), this event procedure displays a message that gives more application-specific information to users.

    To try the example, add the following event procedure to a form that is based on a table with a unique employee ID number as the key for each record.


    <pre>Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Const conDuplicateKey = 3022
    Dim strMsg As String

    If DataErr = conDuplicateKey Then
    Response = acDataErrContinue
    strMsg = "Each employee record must have a unique " _
    & "employee ID number. Please recheck your data."
    MsgBox strMsg
    End If
    End Sub
    </pre>


    Might this be of some use? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

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

    Re: Duplicate Number Warning (2000)

    Steve has pointed you at the Form_Error event. That is probably the best one to use here.

    About the data type mismatch: the field name iMISNumber makes me think it is numeric. In that case, you shouldn't use the quotes qt - that is for text fields.

  10. #10
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Number Warning (2000)

    Yes Equipment schedule is the table and serial number is the field.
    Our serial numbers are alphanumeric so the serial field is a text field

  11. #11
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Number Warning (2000)

    Should the whole line :
    Const qt = """"
    Be removed or changed to sometime else?

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

    Re: Duplicate Number Warning (2000)

    If you do have a numeric field, you can remove that line and replace

    If DCount("[IMISNumber]", "tblAttendees", "[IMISNumber] = " & qt & Me.iMISNumber & qt) > 0 Then

    by

    If DCount("[IMISNumber]", "tblAttendees", "[IMISNumber] = " & Me.iMISNumber) > 0 Then

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Number Warning (2000)

    Hi ready4data

    What is the advantage of using dcount vs dlookup?

    John

  14. #14
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Number Warning (2000)

    Thanks Guys!!!! Using Ready4data's code and Han's edit, it works great.

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Duplicate Number Warning (2000)

    |I'll jump in here and give you an answer, although it may not be *the* definitive answer. Dlookup use the indexes, if any, to find the values in the domain. As soon as it finds a match, it returns a value. DCount executes the equivalent of a single efficient Count(*) on the domain without needing to step through records looking for anything. I would expect DCount to be faster.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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