Full Version: Capture Table Index Violation In An Event
UtterAccess Forums > Microsoft® Access > Access Forms
fizzy1
Hi,
My underlying tblParts has the PartNumber field set to be indexed, with No duplicates. I do this to prevent multiple instances of the same part number being allowed.
When someone tries to enter a duplicate part number in my form, they don't get a warning until they click out of the row (ie: when saving the new record), and it's a pretty esoteric warning at that.
a) Is there some way I can easily test for duplication on updating that control, or,
b) capture the error and send back a better warning? (So what's the error code?) Or,
c) should I just set the record to be Me.Dirty = False upon exiting that control? (Is this last one fairly bullet-proof?)
Doug Steele
You can put code in the form's BeforeUpdate event to check whether the number's already been used.
!--c1-->
CODE
Form_BeforeUpdate(Cancel As Integer)
  
  If IsNull(DLookup("PartNumber", "tblParts", "PartNumber = " & Me!PartNumber)) = False Then
    MsgBox Me!PartNumber & " already exists."
    Cancel = True
  End If
  
End Sub
fizzy1
Hi Doug,
I have tried yours but cannot get it to work, something about my syntax in tweaking your code to match my control name (txtPartNumber) vs field name (PartNumber) isn't playing nice...
I had managed to come up with this code, but it's not very elegant. Think I'd like to get yours running:
CODE
Private Sub txtPartNumber_AfterUpdate()
On Error GoTo EH
    If Me.Dirty = True Then Me.Dirty = False
ExitSub:
    Exit Sub
EH:
    If Err.Number = 3022 Then
        MsgBox "You have entered a Part Number that is already in use." & vbCrLf & "Please enter a different Part Number.", vbCritical, "Duplicate Part Number"
        Me.txtPartNumber.SetFocus
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Description
    End If
End Sub
Doug Steele
What's the code you changed, and what's the error you're getting.
If your control is named txtPartNumber, your code should probably be
CODE
Form_BeforeUpdate(Cancel As Integer)
    
  If IsNull(DLookup("PartNumber", "tblParts", "PartNumber = " & Me!txtPartNumber)) = False Then
    MsgBox Me!txtPartNumber & " already exists."
    Cancel = True
  End If
  
End Sub

This assumes PartNumber is a numeric field. If it's text, that would be
CODE
Form_BeforeUpdate(Cancel As Integer)
    
  If IsNull(DLookup("PartNumber", "tblParts", "PartNumber = '" & Me!txtPartNumber & "'")) = False Then
    MsgBox Me!txtPartNumber & " already exists."
    Cancel = True
  End If
  
End Sub
fizzy1
Ah, yes, that did the trick. It is a text field, sorry, I should have mentioned that...
had wound up with the same code as you did with the numeric data, but struggled to get the syntax for the text version.
This BeforeUpdate version is better than mine as it essentially prohibits me from clicking out of the row. Good stuff!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.