Re: Newbie Forms 5.0

Hi, you are making a common mistake trying to help forms populate records for you - use what they give you ! On the block that needs to be populated automatically create pre-query trigger which will copy values from your ":PS_SYSTEM_RISKS" block, and on ":PS_SYSTEM_RISKS" block create when-validate-record trigger which will do: go_block('PS_SYSTEM_RISKS');
execute_query;

You are done ! No exeption handaling no hassle ! Forms will take care of it for you.

+--------------------------------------------------------------------------+
| Vitaliy Mogilevskiy
| Senior Consultant
| CORE Technology Group, Inc.
| E-mail:    vit100gain_at_earthlink.net
| Web Page:  http://home.earthlink.net/~vit100gain/index.html
|            *** Free DBA Script Library at my Web Page ***
+--------------------------------------------------------------------------+

Ken Brown wrote:

> I'm trying to get the system to bring up an existing record as soon as the
> user completes the components of the PK.
>
> 2 Things
> Can it be done without executing a query.
> If it has to be a query can I do an Enter_query, define query selection and
> Execute Query against When_Validate_Item trigger.
>
> I've been trying the former with the code melow but for some reason the
> rec_counter var will not populate despite the items having valid values.
> (All the values of the PK are populated from lists at run time).
>
> PROCEDURE Get_Curr_Rec IS
>
> rec_counter NUMBER := 0;
> curr_form VARCHAR2(40) := :SYSTEM.CURRENT_FORM;
> curr_block VARCHAR2(40) := :SYSTEM.CURRENT_BLOCK;
> curr_item VARCHAR2(40) := :SYSTEM.CURRENT_ITEM;
> BEGIN
> IF :PS_SYSTEM_RISKS.PS_SYS_TABLE_NAME IS NOT NULL AND
> :PS_SYSTEM_RISKS.PS_SYS_COLUMN_NAME IS NOT NULL AND
> :PS_SYSTEM_RISKS.PS_SYS_COLUMN_VALUE IS NOT NULL THEN
>
> /* THIS BIT FAILS AND ALWAYS RETURNS ZERO RECORDS
> SELECT count(*)
> INTO rec_counter
> FROM ps_system_risks
> WHERE ps_sys_table_name = :PS_SYSTEM_RISKS.PS_SYS_TABLE_NAME AND
> ps_sys_column_name = :PS_SYSTEM_RISKS.PS_SYS_COLUMN_NAME AND
> ps_sys_column_value = :PS_SYSTEM_RISKS.PS_SYS_COLUMN_VALUE;
> */
>
> IF rec_counter = 1 THEN
> SELECT ps_object_desc, ps_object_risk_default, ps_object_risk_max,
> ps_object_risk_min, ps_object_risk_weight, ps_sys_rec_status
> INTO :PS_SYSTEM_RISKS.PS_OBJECT_DESC,
> :PS_SYSTEM_RISKS.PS_OBJECT_RISK_DEFAULT,
> :PS_SYSTEM_RISKS.PS_OBJECT_RISK_MIN,
> :PS_SYSTEM_RISKS.PS_OBJECT_RISK_MAX,
> :PS_SYSTEM_RISKS.PS_OBJECT_RISK_WEIGHT,
> :PS_SYSTEM_RISKS.PS_SYS_REC_STATUS
> FROM ps_system_risks
> WHERE ps_sys_table_name = :PS_SYSTEM_RISKS.PS_SYS_TABLE_NAME AND
> ps_sys_column_name = :PS_SYSTEM_RISKS.PS_SYS_COLUMN_NAME AND
> ps_sys_column_value = :PS_SYSTEM_RISKS.PS_SYS_COLUMN_VALUE;
> ELSIF rec_counter > 1 THEN
> Message(ERROR_TYPE||'-'||TO_CHAR(ERROR_CODE)||':'||ERROR_TEXT||' Too Many
> Records Found In '||curr_form||'.'||curr_block||'.'||curr_item);
> RAISE Form_Trigger_Failure;
> ELSE
> :PS_SYSTEM_RISKS.PS_OBJECT_RISK_DEFAULT := 0;
> :PS_SYSTEM_RISKS.PS_OBJECT_RISK_MIN := 0;
> :PS_SYSTEM_RISKS.PS_OBJECT_RISK_MAX := 0;
> :PS_SYSTEM_RISKS.PS_OBJECT_RISK_WEIGHT := 0;
> :PS_SYSTEM_RISKS.PS_OBJECT_DESC := NULL;
> :PS_SYSTEM_RISKS.PS_SYS_REC_STATUS := 'Active';
> END IF;
> END IF;
>
> EXCEPTION
> WHEN Form_Trigger_Failure THEN
> Message(ERROR_TYPE||'-'||TO_CHAR(ERROR_CODE)||':'||ERROR_TEXT||' Error
> Encountered In '||curr_form||'.'||curr_block||'.'||curr_item);
> RAISE;
>
> END;

--