Re: How to use sequences, and which trgger for "Execute_Querry'?

"Richard Hollingsworth" <william.hollingsworth_at_hsv.boeing.com> wrote in message news:39DB57C0.260EC723_at_hsv.boeing.com...
> Matt:
>
> I don't know if this will get to you or not, never replied to a sender
 before.
>
> What I want to do is, when the user enters the form, it takes them to the
 "NEXT"
> blank record, and shows the new sequence number in the CR_NUMBER field.
 Each time
> the user presses the INSERT_KEY on the toolbar, I want to create a new blank
 record
> and increment the sequence number and display it in the CR_NUMBER field.
>
> I'm guessing that this is kinda the standary interface a user would want to
 see
> when entering a new form - to see the new cr_number (non-editable of course)
 and
> then start entering data.
>
> Thanks for the reply and any help you might have.
>
> Thanks again,
> Richard Hollingsworth

Ah - I see. In that case I would put the code in WHEN-NEW-RECORD-INSTANCE but surround it with an IF condition:

IF :SYSTEM.RECORD_STATUS = 'NEW' THEN
   SELECT MY_SEQ.NEXTVAL FROM DUAL INTO :MY_BLOCK.CR_NUMBER; END IF; This way it will only fire when you land on a record with at status of 'NEW'. If you navigate among any other records (queried records ('QUERY'), queried records that have been changed again ('CHANGED'), records the user just entered but haven't yet been committed to the database ('INSERT')), the sequence logic won't be executed again and again.

However, when the user lands on a new (blank) record, do note that the status will change from 'NEW' to 'INSERT' if either of the following is true:

1. The field you are putting the sequence value into is a database field.
2. The field you are putting the sequence value into is a non-database field but the "Lock Record" property for the field is set to true. This also will include items that are "Display Items" in the property palette because I've discovered that in a non-database item is a display item, Forms sets "Lock Record" property to True behind the scenes *and* the property isn't visible in the palette at all (which I think is a bug or an unwanted "feature" <g!>).

If either of the above is the case, your record status will most likely change from NEW to INSERT and then you'll be asked to commit if you just try and exit the form, WHEN-VALIDATE-RECORD will fire, etc. To make sure, debug it:

message(:SYSTEM.RECORD_STATUS); pause;
IF :SYSTEM.RECORD_STATUS = 'NEW' THEN
   SELECT MY_SEQ.NEXTVAL FROM DUAL INTO :MY_BLOCK.CR_NUMBER; END IF;
message(:SYSTEM.RECORD_STATUS); pause;

If you don't want that to happen (the action of just populating the sequence on the record without the user doing anything causes the record status to change), there's more coding involved.

If that's the case, try one of two things (can't remember which one works for this). Use an invisible control item for your sequence number (like CR_DUMMY_NUMBER) to be selected into (like in a control/non-database block) and then for your "real" CR_NUMBER field, you either set the Default Value property or the Synchronize With Item property (can't remember which of the two) to refer to the control item. I think it's Default Value so try that first, but I'm not 100% sure. Then debug it in WHEN-NEW-RECORD-INSTANCE:

message(:SYSTEM.RECORD_STATUS); pause;
IF :SYSTEM.RECORD_STATUS = 'NEW' THEN
   SELECT MY_SEQ.NEXTVAL FROM DUAL INTO :MY_CONTROL_BLOCK.CR_DUMMY_NUMBER; END IF;
message(:SYSTEM.RECORD_STATUS); pause;

-Matt Received on Thu Oct 05 2000 - 03:54:04 CEST