Re: Check duplicate key in Forms 4.5

Kevin,

Create a procedure that checks whether the PK values just entered already exist in the database.
In a When-Validate-Record trigger, call the procedure if the status is INSERT.
If successful set a flag, otherwise Raise(form_trigger_failure) to stop navigation.
In a When-New-Record-Instance trigger check the flag, and if set then Post the new details (WNRI because Post is a restricted built-in). If you want to suppress the 'FRM-40404: Database apply complete: 1 records applied' message, set :System.Message_Level := 5 immediately before the Post (then reset after).

If the value of columns upon which the PK is based can be updated then it's a little more complicated - you only want to check for uniqueness when one of these columns is no longer the same as its fetched/posted value (otherwise the uniqueness check will report a non-existent duplicate). Get_Item_Property(item-name, DATABASE_VALUE); is pretty handy for this.

Also, be aware that Post resets the Form Status back to 'QUERY', so you may want to maintain a separate flag to indicate that there are uncommitted changes.

layman_at_poboxes.com wrote in article <3517CB66.6703_at_poboxes.com>...
> Dear all,
>
> We are using Forms 4.5 and there is multi-record base block in
> one of
> our forms. Our users ocassionaly entered duplicate values in this block
> and the server error message will ONLY pop-up when the form is posted,
>
> Is there anyway to check PK uniqueness in multi-record block so as to
> alter user and stop him from further navigation.
>
> Your useful input is highly appreciated.
>
> Regards,
> Kevin
>
Received on Thu Mar 26 1998 - 00:00:00 CET