Error handling with primary key errors in table in data entry

[hedgracer]

I have a table that has the following columns:

Reg
Dept
Electronic Volume
Outcry Volume
Volume

Reg and Dept are primary keys in this table. Sometimes it is necessary to add a reg and dept (this is a Access 2007 front end to an SQL Server 2005 back end table) with the associated volumes. I want to have a usable error message when the user tries to insert a row that has a duplicate reg and dept or a missing reg and/or dept instead of the canned Microsoft error message. I have tried putting the error message in the Form After Update and Form After Insert but this has not worked. Here is the code I have used:

Private Sub Form_AfterInsert()
On Error GoTo err_handler
Me.Requery

endit:
Exit Sub

err_handler:
MsgBox "Error has occurred due to duplicate values or blank lines. Please press the ESCAPE button to delete the bad lines and re-enter the information again.", vbOKOnly
Resume endit

End Sub

Any suggestions? Any help would be appreciated. Thanks.

Dave

 

[MajP]

Check in the before update.

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Not IsFilled Then
    MsgBox "Ensure Dept and Reg are filled in."
    Cancel = True
  End If
  If Not IsUnique Then
     MsgBox "Need unique combination of Reg and Dept. Edit the record."
     Cancel = True
  End If
End Sub
Public Function IsFilled() As Boolean
  If Not (Trim(Me.Reg & " ") = "" Or Trim(Me.Dept & " ") = "") Then
      IsFilled = True
  End If
End Function
Public Function IsUnique() As Boolean
    If Not DCount("*", "tblOne", "Reg = '" & Nz(Me.Reg, "") & "' AND Dept = '" & Nz(Me.Dept, "") & "'") > 0 Then
      IsUnique = True
    End If
End Function