I have a block from a table (PRODUCTOS) with these fields:(PRODUCTO_NO(id),DESCRIPCION,PRECIO_ACTUAL,STOCK_DISPONIBLE)
I need write a code on producto_no field and when executes "when-validate-item" this fill all fields with the correct data.
SELECT producto_no, descripcion, precio_actual, stock_disponible
INTO :productos.producto_no, :productos.descripcion, :productos.precio_actual, :productos.stock_disponible
WHERE producto_no = :PRODUCTOS.PRODUCTO_NO;
This works well, but I want to use, insert_record, delete_record, buttons and this dont "surf" for the database data. Any way of go to wanted record to use on when-validate-item?
Another way: On my Delete button (On-press-button) I have this code:
DELETE FROM PRODUCTOS WHERE PRODUCTO_NO = :PRODUCTO_NO;
COMMIT; --2 VECES SI FUNCIONA
WHEN OTHERS THEN
message('ESE REGISTRO AÚN NO EXIS EN LA BASE DE DATOS. NO SE PUEDE ELIMINAR.');
This only works if I press the button two times, the first time not works :S
First, do NOT use Forms_DDL('Commit')! Using that completely ruins all the automatic processing features that Forms provides!
If you want to populate a new record with data using your select, When-Validate-Item will work, but it should be at the block level, so entering anything in any field will cause the select to run. But it would be tricky -- you would only want to select data into the fields once.
It might be easier if you used a When-New-Item-Instance trigger at the block level. If the trigger detected that :System.Record_Status='NEW', it could run the select.
On your Delete button, all you should do is Delete_Record; Then when you do a commit, Oracle Forms automatic processing will create and run the SQL statement required to remove the record from the database. You really shouldn't put a Commit; in your delete processing. It should only be run from a "Save" or "Submit" button. ....or, you COULD do a Delete_Record; then Commit; That would remove the record and save the changes.
You should never write the sql to delete or update -- Forms does that for you automatically.
The problem, is that I have to get all the values of the select when you write a product number, and I make these how write on first post. Then in my block I haven't record, only text created for my from a select sentence.
Then my DELETE_RECORD not works. I have to use, EXECUTE_QUERY on new form instance of my block? And I have problem selecting the wanted record.
Sorry my english, If you have problems with my explain, I can put some images and more details.
I guess your block is a database block? If so, this is what i guess what happens:
You type in the productnumber in a textfield, then the WHEN-VALIDATE-ITEM-trigger reads all the other values. As you type the values in a new record in a database block, the record is marked as "to be inserted".
Then you issue the DELETE-statement in your button. This will delete the existing record in the database.
Then the COMMIT starts. This commit applies all changes from the form to the DB, and this will issue an INSERT with the data in your block, as it was marked "to be inserted" earlier.
Then the DB-commit is executed and you have deleted a record and at the same time inserted a new one.
Now you your button gets called a second time.
The delete happens as in the first case.
The record in forms was already inserted during the last COMMIT-processing, so this doesn't happen anymore
Then the DB-commit is executed and you have deleted a record.
If you want to use your block for "data selection", you have to change it to ENTER_QUERY-mode first.
Secondly, you should understand the basic oracle from concept/structure. If you under stood, forms flow you requirement will be meet easily.
To your problem,
use Trigger when validate item
And you can use ON_delete/On Insert.
You cannot use go to particular record withing 'when validate item( restricted trigger), instead try to use PUSHBUtto which will enable you using go_record.