Re: Displaying Record Counts in FORMS

From: Todd Owers <ToddO_at_gcr1.com>
Date: 1998/02/13
Message-ID: <01bd38a0$3b414ce0$764c1bcc_at_toddo.gcr1.com>#1/1


Ed,

I don't think you can display '1 of 12' on the message bar, because Forms does not initially retrieve all the records that match the query. It retrieves only a portion of them, based on the block's Records Fetched and Records Buffered settings.

However, you can determine the total number of records that will match the query as follows:

  1. Create a control item to hold the total number of hits; e.g., :my_block.hits.
  2. Create a Key-Exeqry trigger for the block and issue the COUNT_QUERY built-in prior to EXECUTE_QUERY. The COUNT_QUERY built-in will cause a FRM-40355 (Query will retrieve <number> records) message.
  3. Create an On-Message trigger to trap the FRM-40355 message. Use the SUBSTR and RTRIM functions to extract the number of hits from this message, and assign it to a global variable.
  4. Populate the control item with the global variable. (Then, in a Key-Entqry trigger, reset the control item to NULL for the next query.)

Here is the code in the Key-Exeqry trigger: COUNT_QUERY;
:my_block.hits := :GLOBAL.hits;
EXECUTE_QUERY; Here is the code in the On-Message trigger. As you can see, I also display the number of hits in an alert, to give the user the opportunity to cancel the query if the number of records returned is too high. Raise_Query_Alert and Raise_Note_Alert are user-written procedures which simply display an alert with the given text.
DECLARE

  msg_typ  VARCHAR2(3)   := MESSAGE_TYPE;
  msg_num  NUMBER        := MESSAGE_CODE;
  msg_txt  VARCHAR2(80)  := MESSAGE_TEXT;
  xyz      VARCHAR2(30);

BEGIN
  --Display query hits in an alert box.
  IF msg_num = 40355 THEN
    xyz := SUBSTR(msg_txt,21); --Extract the last portion of the 40355 message, which contains the number of hits.

    :GLOBAL.hits := RTRIM(xyz,' records.'); --Strip off the ending. Now only the number of hits remains.

    SET_APPLICATION_PROPERTY(CURSOR_STYLE,'default'); --Eliminate bug # 431338 (hourglass displayed instead of arrow).

    Raise_Query_Alert(msg_txt||' Press ''Execute Query'' to retrieve the records, or press ''Cancel Query'' to enter more restrictive search criteria.');
  ELSE --Print the normal message that would have appeared.     Raise_Stop_Alert(msg_typ||'-'||TO_CHAR(msg_num)||': '||msg_txt);     RAISE Form_Trigger_Failure;
  END IF;
END; [Quoted] Hope this helps.

Todd Owers

Ed Jennings <jenningse_at_mindspring.com> wrote in article <34E3A17C.B1D_at_mindspring.com>...
> After a query is executed, the first row is presented in the
> form. On the message bar, 'Count 1' is displayed indicating
> that this is the first record retrieved. The number increments
> as I page through the result set.
>
> What I would like to see is an indicator of how many records
> were returned from the query. Something more like '1 of 12'.
> Is this possible? If so, how can it be done?
>
> TIA
> Ed Jennings
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~
> jenningse_at_mindspring.com
>
> "The opinions expressed here are my own, not those of DOMAIN
> technologies"
>
Received on Fri Feb 13 1998 - 00:00:00 CET

Original text of this message