Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trapping Duplicate Entries (A2K)

    This may be a repeat of someone else's problem that I haven't been able to locate, but here goes anyway:

    First, the use of a single unique field is not applicable.
    Second, the table being used already has a primary key that can't be fooled with.

    Two separate fields are being entered: Model, which is text & Elevation which is numeric

    Model A Elevation 1
    Model A Elevation 2
    Model A Elevation 3

    I want to 'trap' the entering of: Model A Elevation 2 a second time and display a message that allows the cancellation of the action.

    I thought this was simple, but turned out to be otherwise. For me anyway.
    Thanks in advance for any help from anyone.
    Cheers,
    Andy

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trapping Duplicate Entries (A2K)

    Try <post#=186435>post 186435</post#> .
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping Duplicate Entries (A2K)

    Patt
    Don't you just hate it when the answer is so simple! Thank you kindly. Without pushing my luck, any suggestions on how to get around that really long and boring and confusing message and providing something a little more simple? There has to be a way to 'trap' Billy G.'s verbosity and write in English. Again, many thanks.
    Andy
    Cheers,
    Andy

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trapping Duplicate Entries (A2K)

    It's great when they are that easy !!

    There is a way around that lonnngg message. What you will have to do is to code up the BeforeUpdate event of the form and check for a new record and see if the record already exists by way of a recordset and if so display your own message box.

    Others may have a better way to do this.
    Sorry about that (Haggrad from Harry Potter)
    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping Duplicate Entries (A2K)

    Figures Billy G. wouldn't let us override his serfs easily. You can probably tell by the original question, that there are areas I'm a little bit unsophisticated in. If you happen to know where I could find an example of the Before Update code, you would definitely be on my Good Deed Doers of the Year List.
    Andy
    Cheers,
    Andy

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trapping Duplicate Entries (A2K)

    Here's an example off the top of my head:

    Private Sub form_BeforeUpdate(Cancel As Integer)
    Dim dbs as DAO.Database, rs as DAO.Recordset
    Set dbs = CurrentDB
    Dim sSql as string
    sSql = "SELECT * FROM Tablename"
    sSql = sSql & " WHERE Model = '" & Me!textModel & "' AND Elevation = " & Me!textElevation
    Set rs = dbs.OpenRecordset(sSql)
    If NOT rs.EOF then
    MsgBox "A Record already exists for that Model/Elevation !!"
    Cancel = True
    Endif
    Set rs = Nothing
    Set dbs = Nothing
    End Sub

    You will have to substitute textModel for the control on the form for Model and textElevation for the control on the form for Elevation. Also I have assumed that the fieldnames in the Table are Model and Elevation.

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping Duplicate Entries (A2K)

    Pat:

    Thanks kindly for your code. I appreciate your time. It, and a few other variations on the same theme have once more bailed me out of my Access problems.

    Andy
    Cheers,
    Andy

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping Duplicate Entries (A2K)

    It's been a long time since I played with the Error event so I could be mistaken, but I seem to recall that you can trap that error with the Error event in your form (that's not the same as the On Error... statement).

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Trapping Duplicate Entries (A2K)

    As recommended by D. Martin you can use Form On Error event procedure to trap duplicate unique index/primary key error (Err 3022). You may have noticed that you cannot trap this error using form's Before Update event; you get the generic, verbose error msg when you try to save record that duplicates a unique index. If you use a custom "Save" button you can trap error, but this doesn't work when error results from other action such as user navigating to another record. Example of form Error event:
    <pre>Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Dim strMsg As String
    Select Case DataErr
    Case 3022 'Duplicate value in primary key, unique index, or relationship
    Response = acDataErrContinue
    strMsg = "Cannot save record - duplicate of existing record. " & _
    "Please ensure Model & Elevation are not duplicates."
    MsgBox strMsg, vbInformation, "DUPLICATE RECORD ERROR"
    Case Else
    Response = acDataErrDisplay 'Display default msg
    End Select

    End Sub</pre>

    HTH

Posting Permissions

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