Duplicates custom error Msgbox with lookup

[Guest]

I need to figure out a way to have a msgbox come up after a person enters a
new job number, if it is a duplicate. Right now, I have a msgbox come up in
the Before update Event asking to confirm record change, but it doesn't
lookup to see if it's a duplicate & it also asks to confirm if its a new
record (which I don't want).
This is what I have on my "Before Update" event, under my Job Number field:

dim intResponse As Integer
If Me.Dirty Then
intResponse = msgBox("Accept changes to job number?",vbCritical_
+ vbOkCancel + vbDefaultButton2, "Confirm Job Number")
IF intResponse = vbCancel Then
Cancel = True
Me.Undo
End If
End If


I don't want this Message to come up if the Job number is 0. I want it to
lookup for duplicates & display a custom msg that duplicates are not allowed.
If it is a duplicate, I want it to revert to the old value.

I also have a validation rule for the field:
DLookUp("job","JIT","job=Forms!JIT!job") Is Null
It says that you have entered a duplicate, etc etc... but it doesn't revert
the field to the old value.

Can anyone point me in the right direction on how to have one message for
duplicates & one for just confirming a record change?

Thanks!

 

[Guest]

HI,

Thank you for the code, but my primary key is also the "job" field.
Therefore, this code doesn't work. Could you please update the code for the
pc & the job field being the same?

thanks!