Tip of the Month (September 2011)

Handling 'Duplicate key value'

Visual CE allows you to specify that every record is to have a unique value in a particular column. You set this constraint by selecting FORM | UNIQUE in the form designer. If, when the form is running, the user enters a duplicate value, Visual CE will display the message 'Duplicate key value'. But suppose you wanted to put up a different message (or do something else entirely). Here is how to set this up.

Visual CE has no "error trapping" in 'Duplicate key value', but you can detect this situation in a macro. Let's say you want to make sure that there is no duplicate PART_NO. In the form designer:

(A) Create a filter (EDIT | FILTERS) with the condition PART_NO = @var(45)
(B) Select EDIT | MACROS/EVENTS and create a DUP_CHECK macro:
Step 1: ASSIGN: set @var(45) to PART_NO
Step 2: OPTION | FILTER | PREDEFINED: DUP_CHECK
Step 3: SKIP: if @count > 1 then 0 else 3
Step 4: OPTION | FILTER | OFF
Step 5: MESSAGE BOX: 'Duplicate key value'
Step 6: STOP MACRO
Step 7: OPTION | FILTER | OFF
Step 8: RETURN FROM MACRO
(C) Still in EDIT | MACROS/EVENT, select the EVENTS button and set the "On leaving record" event to DUP_CHECK