Duplicates custom error Msgbox with lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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!
 
Back
Top