Problem with code to catch duplicate Primay Key

[FedBrad]

Yep... So, I did move it to the LostFocus event (for the HICN field), but
still had similar results. I'll fiddle as you have suggested below, and let
you know whre it takes me.

Thanks again... fb

 

[FedBrad]

Marsh,

"You Da Man!" I added the lines you suggested (w/o the DLookup statement),
moved it to BeforeUpdate, and this is now responding exactly as I intended.
However, I seem to have created another challenge in the process.

If I simply go to a new record and enter nothing, then attempt to leave that
record (go back to a different record or close), I get a debug error "Invalid
use of Null" on the following line:

BID = Me.HICN.Value

Mousing over indicates the value is, in fact, being evaluated as Null.
Being a textbox, I would think it would simply see as a zero length string
("") - but not sure if that makes a difference?

If I can't resolve it, are you okay with me including the full event code
for you to look over - may have been lost along the way in this thread...?

 

[Marshall Barton]

"You Da Man!" I added the lines you suggested (w/o the DLookup statement),
moved it to BeforeUpdate, and this is now responding exactly as I intended.
However, I seem to have created another challenge in the process.

If I simply go to a new record and enter nothing, then attempt to leave that
record (go back to a different record or close), I get a debug error "Invalid
use of Null" on the following line:


Mousing over indicates the value is, in fact, being evaluated as Null.
Being a textbox, I would think it would simply see as a zero length string
("") - but not sure if that makes a difference?

If I can't resolve it, are you okay with me including the full event code
for you to look over - may have been lost along the way in this thread...?

A BeforeUpdate event (ehich one? A text box or the form?)
will not fire unless something in the text box or the record
has been changed. So, if you did not do anything on the new
record, then you must have some code somewhere that is
setting a bound contriol (or field). If you do have some
code that does that, it should be removed/revised so it dees
not make the record dirty.

You can post a Copy/Paste of your code, but if there is a
lot of it. I might not be able spot some nuance that is
messing you up.