Solved

After update error

Posted on 2011-03-01
4
201 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:Shen
  • 2
4 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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().

mx
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
Else
' call the Form error handler
Form_Error 3022, 0
End If

End Sub
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
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
Else
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...

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
...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.

;-)

Jeff
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now