• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

After update error

I need help raising an error event after i do a dlookup on a field to check if the value is already in the table.  i ca't raise event and force it to go to form error.
  • 2
1 Solution
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Not quite sure what you are after, but I suppose you could define you own Error constant then use the Err.Raise method of the Err object - see VBA Help for details.  You can't control what is generated by the Form Error event per se ... and not specific error is generated by DLookup().

It sounds like you have some error handling in place so it's difficult to know what a relevant response is but you can do stuff like this..

Private Sub Form_Error(DataErr As Integer, Response As Integer)    ' extracted from help
    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

Private Sub ID_BeforeUpdate(Cancel As Integer)  

If IsNull(DLookup("ID", "Employeesb", "ID= " & Me.  ID)) Then
'do nothing, new number ok
' call the Form error handler
Form_Error 3022, 0
End If

End Sub
Jeffrey CoachmanMIS LiasonCommented:
Perhaps I am missing something, ...or not understanding something...
But you don't really have to "Raise" and error to see if a value exists.

Just use Pete's code like so:
If IsNull(DLookup("ID", "Employees", "ID= " & Me.ID)) Then
'do nothing, new number ok
msgbox "The number exists already."
exit sub
End If

You can set a Public Variable in the code as well, if you need to "keep" a flag for the error...

Jeffrey CoachmanMIS LiasonCommented:
...Thanks, but actually you should split the points with Pete.

I just "ganked" his code and used it in a slightly different manner.

Please click the Request Attention link and ask that the points be split instead.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now