An example given below for Oracle Forms, when a value exists then Execute Query For this value to display the correspondent record else allow user to create a new record for that value.
The following is the example given for HR Schema employee table, in this example user would enter an Empoyee ID and if the Employee ID exists It would query the record else allow user to Create a new record, the trigger written on Key-next-item trigger, you can Download The Employees.fmb form also from the following link EMPLOYEES.FMB
Key-next-item Trigger Code
Declare
V_empid Employees.employee_id%type;
Begin
Select employee_id into V_empid
From Hr.employees
where employee_id =: employees.employee_id;
--Value exists
--Set block property and execute query
Clear_block (no_validate);
Set_block_property (' Employees ', default_where, ' employee_id = ' | | V_EMPID);
Execute_query;
Set_block_property (' Employees ', Default_where, ');
Next_item;
exception
When No_data_found Then
-When is not then clear block and allow to add new
V_empid: =: employees.employee_id;
Clear_block (no_validate);
: employees.employee_id: = V_empid;
Next_item;
End;
If Value Exists then Query Else allow Create New in Oracle Forms an Example