Data validation problem (1 Viewer)

[FISHiEE]

Hi,

I am using the following validation code for the part number field on one of my forms:


DLookUp("[Door Part Number]","2-MAIN DOOR PART DATABASE","[Door Part Number] = '" & [Door_Part_Number] & "'") Is Null And Not ""

The purpose of this is to prevent users from changing/creating parts with a number that already exists in the database (and the last bit prevents no part number being entered).

It is fine for most situations except 1. If the user changes a part number, then changes it back to the original number again, the validation rule thinks this already exists and forces the user to give another part number.

Can anyone suggest how to get around this problem? It is hopefully very simple, but I can't get my head round how to do it!

Many thanks in advance!!

John

 

[WayneRyan]

John,

Make a separate, invisible control called "OriginalValue".

Use the OnCurrent event: Me.OriginalValue = Me.YourKeyField

Use the BeforeUpdate event of Me.YourKeyField:

If Me.YourKeyField <> Me.OriginalValue And Not IsNull(DLookUp(...)) <-- Invalid!!

Wayne