Standard forms don't work like that.

To insert or update a record, you just type in new values, and press a button that does a Commit_Form. Forms does the inserting for you.

So all you need to do is make sure the required fields are entered. To do that, you write code in a when-validate-record trigger, and if a field is not entered, you display your message, either using an alert or a simple Message() command, and then Raise Form_Trigger_failure; This causes processing to stop until you fill the fields.

I figured out a solution, don't know if it is the most efficient, but it works.

I have a save button that contains the following.

update_record;
commit;

and an ON-ERROR trigger on the block with the following to check not null and foreign key integrity....

DECLARE
edit_alert alert;
null_values boolean := false;
invalid_values boolean := false;

cou_id hr_countries.country_id%TYPE;

null_message varchar2(200) := 'CANNOT BE NULL:' || CHR(10);
invalid_message varchar2(200) := 'INVALID VALUES:' || CHR(10);
full_message varchar2(400) := '';
choice number;
BEGIN
IF :locations.city IS NULL THEN
null_values := true;
null_message := null_message || '----- City' || CHR(10);
END IF;

IF :locations.country_id IS NOT NULL THEN
BEGIN
SELECT country_id
INTO cou_id
FROM hr_countries
WHERE UPPER(country_id) = UPPER(:locations.country_id);
EXCEPTION
WHEN OTHERS THEN
invalid_values := true;
:locations.country_id := null;
invalid_message := invalid_message || '----- Country Id' || CHR(10);
END;
END IF;

IF null_values = true OR invalid_values = true THEN
IF null_values = true THEN
full_message := full_message || null_message;
END IF;
IF invalid_values = true THEN
full_message := full_message || invalid_message;
END IF;
edit_alert := Find_Alert('EDIT');
set_alert_property(edit_alert, ALERT_MESSAGE_TEXT, full_message);
choice := show_alert(edit_alert);
END IF;
END;

Thanks for your help.

Michael

The alert is required, which is why I have no choice to use it. I must have custom error messages. Using a When-Validate-Record trigger was not working for me because it posed the same problem with it not seeing the value of the variable and not being able to validate it. I even tried using commit_form, etc. I followed your instructions to a tee, but to no avail.

I do have multiple blocks, about 26 to be exact...some tabbed and others stacked. I didn't followup with the regions because I ended up not needing validation on it because in my edit forms, I made the primary keys un-editable.

Thanks for your help Steve....but at this point, with the due date so near, I think I will need to stick with what works, and play around with it later.

Michael

Steve said standard forms don't work that way. Your teacher may want to see working programs but (s)he might put more emphasis on your understanding of the forms environment, in which case using standard practices would be best. It's better to be standard in the real world too, although sometimes you really do just have to get something to work before a deadline.

As an example of non-standard practices being bad, consider your idea of having validation/committing in a button. This is Ok as long as the user always presses the button but there are other ways to commit the data, such as closing the form, executing a query or navigating to a new record in a master block. That validation code will not run in those circumstances. Always use a when-validate-* trigger or the property pallette for validation.

Your on-error trigger is also bad. Firstly, do you know why it is firing? Is it because there's a not-null restriction on the region_id item and a null value in the field? If it's firing for any other reason, such as because there is a duplicate record or the database has shut down, then the user will never know because you're overriding standard functionality. If it is firing because of the not-null restriction then a user who does put a value in there will not have their country_id checked at all.

There may be other reasons as well but the on-error trigger is certainly firing because you're calling the update_record procedure in a button. That built-in is only allowed in an on-update trigger (it's annoying that forms will compile with errors like this but that's just the way it is). This will only cause it to fire once, so only the current record will be validated.

Your foreign key integrity check is wrong. You're using

WHERE UPPER(country_id) = UPPER(:locations.country_id);

so if I enter FranCe and FRANCE is on the database the program will not see a problem. That's Ok as long as throughout the system you refer to upper(country_id), but this will negate an index on that column so should be avoided. The standard method is to use an LOV and to set the item to 'Validate from List = true' (property pallette). Also, it's very rare to validate an item other than in its own when-validate-item trigger. The exception is paired items such as the start/end of a date range, where one date must precede the other and it's more user-friendly to validate both fields together.

I can't see why the "if...is null" won't work in the WVR trigger, but if you use standard practices throughout the form and that is the only problem then you're much better off than now.

For the path, use "get_form_property(<module_name>,file_name)" and strip off the <module_name>.fmx part. It seems you have a valid reason for not being standard here (using environment variables) so that's Ok . Good luck with the assignment.

If you validate in the on-error trigger then you cannot be sure that each record will be validated. The trigger will only fire when an error is encountered, so it might not fire at all, and only the values in the fields on the current record (the record the user is on when the error happens) can be read. Move the validation to the vhen-validate-record trigger and then fix the problem in that trigger. If you delete the on-error trigger it might help you find the problem. Put a message in the when-validate trigger to make sure the trigger is firing and to show the value and length of data in the field you're testing.

On-Error triggers don't use sqlcode and sqlerrm. Check the forms help for how to trap specific errors in on-error and on-message triggers.

Your teacher probably has an environment set up where they can put all forms/menus/libraries into a directory and run the required form. Unless you've been told otherwise, it's probably best to ignore the details of how the forms will be run.

DECLARE

  "variables"

BEGIN

  IF MESSAGE_TYPE='FRM' AND MESSAGE_CODE=50026 THEN

    "custom alert for date format"

  END IF;

END;

I always put the on-error trigger at the form-level, but I believe your block-level triggers should be ok. If you put it at the form-level, yours could be coded like this:

DECLARE

  Err_Code NUMBER(5)     := ERROR_CODE;

  MSG     VARCHAR2(150)

          := SUBSTR('   '||ERROR_TYPE||'-'||TO_CHAR(Err_Code)||': '

                                     ||ERROR_TEXT,1,150);

BEGIN

  IF Err_Code=50026 THEN

      "custom alert for date format";

  ELSE

    Message(MSG);

  END IF;

  Raise form_trigger_failure;

END;