?
Solved

After update error

Posted on 2011-03-01
4
Medium Priority
?
207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 75
ID: 35007544
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
ID: 35007775
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 2000 total points
ID: 35008330
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
ID: 35018301
...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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

777 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