this coding giving me only one record i.e the last record of the table, whereas the table has 50 records. how can i get all records from the table in the form datablock.[EDITED by LF: applied [code] tags]

Is there any reason for doing it this way (i.e. in a loop)? Under normal circumstances, you'd create a data block (based on a table), run the form and execute query - it would be populated with data. If necessary, you'd even enter some search criteria (which would evaluate to a WHERE clause, somewhere behind the scene).

It appears that you are fighting Forms instead of making friends with it. Did you ever read any documentation or attended classes or something? If not, perhaps you should because - the way you are (trying to) work with Forms is ... hm.

sorry for my poor concept but i am totally new in oracle forms and i didn't get any training on this platform.
as you said-- Is there any reason for doing it this way (i.e. in a loop)? Under normal circumstances, you'd create a data block (based on a table), run the form and execute query - it would be populated with data. If necessary, you'd even enter some search criteria (which would evaluate to a WHERE clause, somewhere behind the scene).

before the loop i did it with the same concept(create a data block (based on a table), run the form and execute query) an it was working fine.
but now i want only distinct records from the view as i discussed this on http://www.orafaq.com/forum/t/181586/129190/

now i getting the records in right way (not overwriting in the same row).
but i am getting a error message FRM-40102:Record must be entered or deleted first, then clicking on OK of this message the next row comes in form.

declare
qry varchar2(5000);
n number;
alert number;
cursor c1 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO ;
cursor c2 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where regno= :control.v_regno or form_no= :control.v_form_no ;
Begin
if :control.rg='B' then
if :control.REC_FROM is not null and :control.REC_TO is not null then
IF :control.v_form_status is not null then
qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO and a.form_status=:control.v_form_status ';
set_block_property('ol_registration', default_where, qry);
go_block('ol_registration');
clear_block(no_validate);
-- message('1111'); message('');
execute_query;
END IF;
-- message('bbbb'); message('');
IF :control.HOUSE_TYPE is not null then
qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO and b.qtr_type=:control.house_type ';
set_block_property('ol_registration', default_where, qry);
go_block('ol_registration');
clear_block(no_validate);
--message('2222'); message('');
execute_query;
END IF;
-- message('cccc'); message('');
IF :control.HOUSE_TYPE is not null and :control.v_form_status is not null then
qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO and qtr_type=:control.house_type and form_status=:control.v_form_status ';
set_block_property('ol_registration', default_where, qry);
go_block('ol_registration');
clear_block(no_validate);
-- message('3333'); message('');
execute_query;
END IF;
-- message('dddd'); message('');
------
IF :control.status is not null then
qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO ';
IF :control.status='edt' then
qry :=qry|| ' order by EDT ';
ELSIF :control.status='form_no' then
qry :=qry|| ' order by FORM_NO ';
ELSIF :control.status='name' then
qry :=qry|| ' order by NAME ';
ELSIF :control.status='recd_on' then
qry :=qry|| ' order by RECD_ON ';
END IF;
else
qry:=qry||' ORDER BY EDT,NAME,FORM_NO,RECD_ON ';
set_block_property('ol_registration', default_where, qry);
go_block('ol_registration');
clear_block(no_validate);
-- message('4444'); message('');
execute_query;
END IF;
-- message('eeee'); message('');
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c1;
loop
fetch c1 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c1%notfound;
next_record;
end loop;
close c1;
first_record;
else
message('Please fill Received from and Received to date(s).'); message('.');
raise form_trigger_failure;
end if;
elsif :control.rg='S' then
if :control.v_regno is not null or :control.v_form_no is not null then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c2;
loop
fetch c2 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c2%notfound;
next_record;
end loop;
close c2;
first_record;
end if;
end if;
END;

this is my coding, in this cursor c1 and c2 is working fine but all the coding doing before it which retrive data through set_block_property('blockname', default_where, qry); execute_query;
is not working as the conditions given in IF statement. it giving the data same as from cursor c1.

CM: fixed the code tags. They need to be wrapped in square brackets []

Begin
if :control.rg='B' then
---------------------------------------------------
if :control.REC_FROM is not null and :control.REC_TO is not null then
--------------------

--------------------
IF :control.HOUSE_TYPE is not null and :control.v_form_status is null then
qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO
and qtr_type=:control.house_type ORDER BY :control.status ';
set_block_property('ol_registration', default_where, qry);
go_block('ol_registration');
clear_block(no_validate);

execute_query;
END IF;
--------------------
--------------------
IF :control.HOUSE_TYPE is not null and :control.v_form_status is not null then
qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO
and qtr_type=:control.house_type and form_status=:control.v_form_status
ORDER BY :control.status';
set_block_property('ol_registration', default_where, qry);
go_block('ol_registration');
clear_block(no_validate);

execute_query;
END IF;
--------------------

-------------------
IF :control.HOUSE_TYPE is null and :control.v_form_status is null then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c1;
loop
fetch c1 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c1%notfound;
next_record;
end loop;
close c1;
first_record;
END IF;
-------------------
-------------------
IF :control.v_form_status is not null and :control.HOUSE_TYPE is null then
go_block('ol_registration');
clear_block(no_validate);
--first_record;
open c3;
loop
fetch c3 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c3%notfound;
next_record;
end loop;
close c3;
first_record;
END IF;

--------------------

------------------
else
message('Please fill Received from and Received to date(s).'); message('.');
raise form_trigger_failure;
end if;

------------------------------------------------

elsif :control.rg='S' then

if :control.v_regno is not null or :control.v_form_no is not null then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c2;
loop
fetch c2 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c2%notfound;
next_record;
end loop;
close c2;
first_record;
end if;
end if;
END;

/code

in this form i have a (sorted by) list item, the items in the list item are form_no, name, regno, edt and one blank. when i select form_no in list item, then the data should be come in order by form_no and same for the other items and when i select blank in list item data should come in order by form_no, edt, regno, name. how i get the result in order by if i select any item in list item.

thanks for your help. now i am getting data in sorted form through that list item but only in set_block_proerty part. i want the same for the data coming through cursor c1 and c3 also. what should i do for this, as ORDER BY :control.status is not working here.

i tried this before as
order by case
when :control.status = 'edt' then edt
when :control.status = 'name' then name
when :control.status = 'form_no' then form_no
when :control.status = 'recd_on' then recd_on
else name
end ;

but that was showing an error
type mismatch found at 'EDT' among result expression in a CASE expression

cursor c4 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
order by case
when :CONTROL.STATUS = 'edt' then edt
when :CONTROL.STATUS = 'name' then name
when :CONTROL.STATUS = 'form_no' then form_no
when :CONTROL.STATUS = 'recd_on' then recd_on
when :CONTROL.STATUS = '' then name
[i]--else edt,name, form_no, recd_on[/i]
end ;

with this i am getting error: type mismatch found at 'EDT' among result expression in a CASE expression.

and when this code with else edt,name, form_no, recd_on, i am getting error:
encountered symbol "," while expecting one of these........
but what i know we can use multiple columns in order by statement.