How do I resolve FRM-40102: Record must be entered or deleted first

what do I want to do? If No is chosen for delete, I want to set the records
marked for delete with an asterisk to null; otherwise delete the records.  Below is what I have and it gives me the FRM-40102: record must be entered or deleted first, then it goes to bottom of form for new record insert.  Help!  What am I doing wrong?
Declare
     Cancel_counter number(6) := 0;
Begin
     go_block('dup_member');
     go_record(1);
     go_item('dup_member.prop_no');
     set_item_property('dup_member.del_ind', displayed, property_true);
     if :dup_delete_block.yes_no = 'N' then
     while :counter_block.record_count != cancel_counter
         loop
             if :dup_member.del_ind = '*' then
             :dup_member.del_ind := null;
             cancel_counter := cancel_counter + 1;          
             end if;
          if :system.last_record = 'FALSE' then
          next_record;
         end if;
     end loop;
fati_sAsked:
Who is Participating?
 
MarkusIdConnect With a Mentor Commented:
So there is still the question how the :counter_block.record_count.

Why don't you only go through all the records (well, if you're at the last record, there can't be any more record with an asterisk, even if :counter_block.record_count tellslls you other things.)

So I would suggest the following:

IF :dup_member.card_no is not null THEN
                next_record;
ELSE
  cancel_counter := :counter_block.record_count;
              END IF;
0
 
jwahlCommented:
is :dup_member.del_ind mandatory? if so, you can't leave the record if the value is null ...
0
 
MarkusIdCommented:
Is this as queried data block? The problem is that :system.last_record doesn't bring back 'FALSE' when you are on the last record, so you go to the next empty record. As :counter_block.record_count still doesn't equal cancel_counter, the programs wants to go to the next record, whisch is not possible, because the current record is empty.

If the block is not queried, or new data inserting into the block is allowed, it is possible that Oracle Forms doesn't bring back 'FALSE' if you are on the last record.

There may be 2 variants of resolving this:
1) Set the block_property 'INSERT_ALLOWED' of block 'dup_member' to false

2) Instead of :system.last_record = 'FALSE' check, if the key-fields are filled (if not, it is a new record and you don't have to go to the next record)
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
fati_sAuthor Commented:
This is a query data block.  How do I check if the key fields are filled?  I tried

Declare
  Cancel_counter number(6) := 0;
  chk_rec  varchar2(40);
Begin
     go_block('dup_member');
     go_record(1);
     go_item('dup_member.prop_no');
     set_item_property('dup_member.del_ind', displayed, property_true);
     if :dup_delete_block.yes_no = 'N' then
       
        while :counter_block.record_count != cancel_counter
          loop
            if :dup_member.del_ind = '*' then
              :dup_member.del_ind := null;
              cancel_counter :=cancel_counter + 1;
            chk_rec := :dup_member.card_no is not null;        
            end if;
          end loop;

expression is of wrong type for this line:  chk_rec := :dup_member.card_no is not null;
         
             
0
 
jwahlCommented:
this is wrong syntax - try

IF :dup_member.card_no is not null THEN
...
END IF;
0
 
MarkusIdCommented:
Try this one (I suppose :dup_member.card_no is a field not allowed to be null in a queried record)
Declare
  Cancel_counter number(6) := 0;
  chk_rec  varchar2(40);
Begin
     go_block('dup_member');
     go_record(1);
     go_item('dup_member.prop_no');
     set_item_property('dup_member.del_ind', displayed, property_true);
     if :dup_delete_block.yes_no = 'N' then
       
        while :counter_block.record_count != cancel_counter
          loop
            if :dup_member.del_ind = '*' then
              :dup_member.del_ind := null;
              cancel_counter :=cancel_counter + 1;
              IF :dup_member.card_no is not null THEN
                next_record;
              END IF;
            end if;
          end loop;

Open in new window

0
 
fati_sAuthor Commented:
Thanks!  The syntax provided ran the code but unfortunately did not provide the solution I need.  
Please help.  My requirement is When No is selected, I want to loop through all the records that
have del_ind set with asterisk and set them to null.  I also want to be able to repeat this multiple times.

What has been happening is that when I select No, the program hangs; if I select Yes to delete, it deletes but if I remain in the same block and want to repeat the delete process, it hangs.  I can't seem to get beyond these two issues.

I have attached a file with the delete procedure I'm using too.
Any assistance is appreciated.
delete-problem.txt
0
 
MarkusIdCommented:
What does :counter_block.record_count do? There might be the problem, that you don't mark as many records as deleted as the number in this field. So you get stuck in a never-ending loop.
0
 
fati_sAuthor Commented:
:counter_block.record_count holds the number of records with asterisk (selected for delete).

I suspected that. So I tried
  While :counter_block.record_count > 0
    loop
.........

and it still hangs.  Can you suggest another logic to use to go through the records that have asterisk for deletion?
0
 
fati_sAuthor Commented:
Thank you...this worked~
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.