If duplicate is found,.....

[cookitup]

I have a field that I am using code to find a duplicate on BeforeUpdate. That part of the code works fine, however, I would like for the field to either sav a null or zero value in and not allow the AfterUpdate on the same field to work. Here is the code I am using below:

If DCount("[Job Number]", "[Active]", "[Job Number]='" & Me.Job_Number & "'") > 0 Then
MsgBox "This Job Number has already been used"
Me.Job_Number = 0
End If
I know the problem is with the Me.Job_Number = 0, as this returns an error that says that "The Macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing (DB Name) from saving the data in the field" Debug takes me to the Me.Job_Number = 0 line. Please help

 

[jdraw]

If your intent is to prevent duplicate Job Numbers in your table, then you could make [Job Number] indexed with no duplicates -- Access will tell you if the record already exists. You can trap that error and take appropriate action.

It is unclear to me what you are trying to do by setting, or attempting to set, the Job Number to 0. Why not an error message, or information message to the user???

I'm sure you have some rationale but it isn't clear.