OraFAQ Forum: Forms » disable previous record;

Home » Developer & Programmer » Forms » disable previous record;

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

disable previous record; [message #689404]

Mon, 11 December 2023 23:17

shahzad-ul-hasan
Messages: 640
Registered: August 2002

Senior Member

i have master -detail table. which has 40 record in master and 80 record in detail. i want to restrict user to only enter/update record the current month. the previous month record should be disable.
i have write code in when_new_form_instance but not succeed.

declare 
a varchar2(30);
begn
 select distinct fmonth into a from tfee
group by stuid,fmonth;
 if a <> to_char(sysdate,'MONTH') then
   set_item_property('PAID',Enabled,Property_false);
   set_item_property('TDATE',Enabled,Property_false);
   set_item_property('TDAMT',Enabled,Property_false);
   set_item_property('TDATE2',Enabled,Property_false);
 else
   set_item_property('PAID',Enabled,Property_True);
   set_item_property('TDATE',Enabled,Property_True);
   set_item_property('TDAMT',Enabled,Property_True);
   set_item_property('TDATE2',Enabled,Property_True);
end if;
end;

Report message to a moderator

Re: disable previous record; [message #689406 is a reply to message #689404]

Tue, 12 December 2023 01:28

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Code you posted is invalid (BEGN?). Apart from that, it'll most probably raise TOO_MANY_ROWS because that SELECT sooner or later will return more than a single row. Also, trigger seems to be wrong; it fires when form starts, and - at that moment - you don't know which master records user will deal with - it depends on query they perform.

It is unclear what data is stored into TFEE.FMONTH column; the way you put it, it should be a CHAR (not VARCHAR2!) column whose length is long enough to hold the longest month name because TO_CHAR(SYSDATE, 'MONTH') returns CHAR datatype and values are right-padded with spaces up to length of the longest month name.

SQL> select length(to_char(date '2023-06-01', 'MONTH')) june,           --> "June" is 4 characters long
  2         length(to_char(date '2023-12-23', 'MONTH')) december        --> "December" is 8 characters long
  3  from dual;

      JUNE   DECEMBER
---------- ---------- 
         9          9                                                   --> however, both are reported to be 9 characters long!

SQL>

Furthermore, month name only doesn't guarantee that it is about current month of current year. Maybe you're really restricting it to month only; can't tell. Also, your code says that FMONTH contains month names in UPPERCASE; if that's not so, fix code (otherwise, "december" won't match "DECEMBER").

Therefore, I'd suggest you use WHEN-NEW-RECORD-INSTANCE trigger on master block level which does something like this (presuming blocks are named "master" and "detail"):

if upper(trim(:master.fmonth)) <> trim(to_char(sysdate, 'MONTH')) then
   set_block_property('detail', insert_allowed, property_false);
   set_block_property('detail', update_allowed, property_false);
else
   set_block_property('detail', insert_allowed, property_true);
   set_block_property('detail', update_allowed, property_true);	
end if;

If you try to modify values in detail block rows, you'll get "FRM-40200: Field is protected against update" error.

If you try to insert values into detail block, you'll get "FRM-41051: You cannot create records here".

Report message to a moderator

Re: disable previous record; [message #689409 is a reply to message #689406]

Wed, 13 December 2023 06:23

shahzad-ul-hasan
Messages: 640
Registered: August 2002

Senior Member

When new Record Insatnce


declare 
	a number;
	b number;
begin
	select max(sno) into a from tfee
	where stuid=:stuu.stuid
	group by stuid;
	if :sno <> a  then
  message('You cannot update/insert the previous month record.');
  message('You cannot update/insert the previous month record.');
  set_item_property('TAMT',update_allowed, property_false);
	set_item_property('ARREAR',update_allowed, property_false);
	set_item_property('PAID',update_allowed, property_false);
	set_item_property('TDATE',update_allowed, property_false);
	set_item_property('TDATE2',update_allowed, property_false);
	set_item_property('TDAMT',update_allowed, property_false);
	set_item_property('TAMT',insert_allowed, property_false);
	set_item_property('ARREAR',insert_allowed, property_false);
	set_item_property('PAID',insert_allowed, property_false);
	set_item_property('TDATE',insert_allowed, property_false);
	set_item_property('TDATE2',insert_allowed, property_false);
	set_item_property('TDAMT',insert_allowed, property_false);
else
	set_item_property('PAID',update_allowed, property_true);
	set_item_property('TDATE',update_allowed, property_true);
	set_item_property('TDATE2',update_allowed, property_true);
	set_item_property('TDAMT',update_allowed, property_true);
end if;
end;

thx for your sharing. this code has made me joy.thanks

Report message to a moderator

Re: disable previous record; [message #689410 is a reply to message #689409]

Wed, 13 December 2023 07:31

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You're welcome.

In SELECT statement you wrote: remove GROUP BY clause, it doesn't do anything.

Also, consider naming variables with a more meaningful names. "a" is pretty much useless and you have to scan through code to find out what it actually means. Why not L_MAX_SNO, for example? "L" for a "local variable" and "MAX_SNO" because it is descriptive.

Don't declare variables you don't use (that would be "b" in your example).

Report message to a moderator

Previous Topic:	ON-LOGON trigger not working (merged)
Next Topic:	Alphanumeric

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 21 12:55:15 CST 2024