Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi is there a more intuitive way I can display thee standard "The changes you requested to the table were not successful because they would create duplicate values in the index" replaced with a user message of my own?


    Thanks, Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You cannot do this at the table or query level, but you can use the On Error event of the form used for data entry. The error number of the duplicate index error is 3022.

    For example, if there is a field ID that must be unique:

    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
      Select Case DataErr
    	Case 3022 ' duplicate index
    	  ' Activate offending control
    	  Me.ID.SetFocus
    	  ' Display custom message
    	  MsgBox "You have entered a duplicate ID value, you dummy!", _
    		vbExclamation
    	  ' Suppress built-in error message
    	  Response = acDataErrContinue
    	Case Else
    	  ' Display built-in error message
    	  Response = acDataErrDisplay
      End Select
    End Sub

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='iksotof' post='777266' date='28-May-2009 20:25']Hi is there a more intuitive way I can display thee standard "The changes you requested to the table were not successful because they would create duplicate values in the index" replaced with a user message of my own?


    Thanks, Darren.[/quote]
    I do this in two ways:
    • Use the Before Update event of the form to count whether any records already exist that would lead to a duplicate. If so display your own message, then Cancel the update, or
    • If I am using combo boxes, only offer values that have not already been used, so that you can't use values that would lead to duplicates.
    Regards
    John



  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks guys, both most helpful. John I am intruiged with your combo box solution. What sort of code would I use to do this?


    Cheers, Darren.

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In an earlier post I asked if there wa sa way of trapping the index duplicate keys message that shows and I was givne this reply by John H...

    I do this in two ways:

    "Use the Before Update event of the form to count whether any records already exist that would lead to a duplicate. If so display your own message, then Cancel the update, or
    If I am using combo boxes, only offer values that have not already been used, so that you can't use values that would lead to duplicates."

    This looks cool but I am unsure how to code. Any guiance would be great.

    Thanks, Darren

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='iksotof' post='778134' date='03-Jun-2009 11:23']In an earlier post I asked ...[/quote]
    I have moved your new topic into the original topic because it's a follow-up question.

    See the attached sample database. Both approaches are demonstrated in frmDemo.

    The Before Update event of the form tests whether the value entered for UniqueNumber hasn't already been used:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      If DCount("*", "tblTest", "UniqueNumber = " & _
    	  Me.UniqueNumber & " AND ID <> " & Me.ID) > 0 Then
    	Me.UniqueNumber.SetFocus
    	MsgBox "The value " & Me.UniqueNumber & _
    	  " has already been used.", vbExclamation
    	Cancel = True
      End If
    End Sub
    The combo box for UniqueLookup has a row source that only displays available values:

    SELECT ID, Description FROM tblLookup WHERE ID Not In (SELECT UniqueLookup FROM tblTest) OR ID = Forms!frmDemo!UniqueLookup

    The combo box is requeried when the user moves to another record:

    Code:
    Private Sub Form_Current()
      Me.UniqueLookup.Requery
    End Sub
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As ever Hans, most obliged.

    Darren.

Posting Permissions

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