Prevent submission of data to database with a macro

English
, ,
#1

In Base, I need to prevent new records in a form from being saved (or records from being updated) with a certain field being null. I could put a NOT NULL constraint on that field, but that would require changing a lot of old records, which I need to avoid for the sake of historical integrity. I think I need to use a macro assigned to the “Before submitting” event of the form, and I know it needs to contain an if statement about that field’s control.selectedValue being empty or null, but I haven’t the foggiest clue what commands to use to prevent the submission.

Edit: I just came across the information that returning false will prevent submission. But I’ve never dealt with returning values from sub’s before. How is that done?

#8

I just read in AndrewMacro that macros cannot return values, and functions are needed. I’ll try putting a function in for the event and see if that works.

#23

I would try hard to resolve the issue on that level. Make data consistent and the problem will be solved.

FormAction.odb (13.2 KB)
Does this help a little bit? I really hate writing Base macros. Sometimes, the routine is called by a form controller, sometimes it is the form itself. In either case it is extremely difficult to react adequately.

Edit: I forgot to add the error handler. To avoid the error message in case of rejection, assign an empty routine to the form’s error event.

#3

Alas and alack! The “Before submitting” event on my form will not launch macros or functions. The form just submits. Methinks this is a bug.

#4

I just tried before record change, since it’s also a vetoable event, and it seems to work.

#25

Reacting adequately shouldn’t be difficult in this case, though, the way I read the documentation. Returning True should allow submission. Returning False should veto it. I don’t understand how what calls the routine has to do with it.

Edit: I think I’ve just waded in past my skill and intellect level.

#5

I went ahead and created a simple test case to verify that, in general, “Before submitting” for forms does not fire. Then I filed a bug report. This is kind of a big deal, because it prevents vetoing of submission.

#6

https://bugs.documentfoundation.org/show_bug.cgi?id=148853

#tdf148853

#7

Yup. That’s my bug.

#9

As an alternative, I started looking into creating a trigger on the MySQL side. I came across the first answer in

How to abort INSERT operation in MySql trigger? - Stack Overflow.

I adapted that to my situation:

CREATE TRIGGER partnos_prevent_no_enteredby
BEFORE INSERT ON partnos
FOR EACH ROW
BEGIN
	DECLARE msg varchar(255);
	IF NEW.enteredby IS NULL THEN
		set msg = "The Entered By filed must be filled in before submission.";
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
	END IF;
END$$

Unfortunately, it throws multiple syntax errors, apparently starting with the “BEGIN” statement. I’m using MySQL 5.7. I’ve checked the manual for 5.7, and the Begin…End statement should be available. I’m a little bit dubious about having those semicolons in the middle of my trigger definition.

#10

Your database has no table, no form, no macro. It is as blank as can be.

#11

We do not even know the type of database! If it happens to be embedded HSQL, that one is documented here: Chapter 9. SQL Syntax

#12

Apologies. I must have uploaded that .odb file before I hit “Save” in Base. I shall upload one with contents. I used HSQL for my test case/demonstration. However, for the database I care about, I’m using MySQL 5.7. That’s where I tried the trigger workaround.

#13

Hello @agerber85
Just tested Before Submitting event using HSQLDB and had no problem. As you noted it needs to be a function. But you must return True if the update is to occur and False if not to occur (data to be rejected must still be cleared).

#14

Right. The Test Database.odb wasn’t expected to prevent submission, only to throw a msgbox, which it doesn’t do. That’s all I was demonstrating. No msgbox = macro did not run = event did not fire.

#15

Did not try your Test DB - did my own without an issue. Displayed message & followed True/False result.
Event fired.
I am using:

Version: 7.3.2.2 / LibreOffice Community
Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
#22

Thanks! That sounds like exactly what I need. All I need is to prevent the addition of records without a value in that specific field (enteredby). The rest (clearing all other fields, closing the form without saving data) is up to the user. Unfortunately, I’ve come up against a wall where my version of MySQL doesn’t like the syntax you used, either. It says the syntax is near ‘End If’, which I take to mean it doesn’t like what’s before the End If statement (the SIGNAL statement).

#16

Sounds like I need to upgrade LO. I generally try to stay on the still track rather than the fresh track to minimize bugs, but for this bug, I might make an exception. (I really hope the difference is in the version, not the platform. Linux is not really an option for this application.)

#17

Just upgraded LO to 7.2.6.2 (the latest Still) and still no joy. It is still not throwing the msgBoxes.

#24

Thanks, everyone for your help! I have determined that I just have too many problems (and not enough knowledge or skill) with getting any method/approach to preventing submission (either through a macro or by setting up a trigger in MySQL) to conditionally prevent submission. Instead, I am going to prevent the form from being filled out by having controls disabled initially, and then having a macro that enables them once the necessary field has been filled in (something I usually do for cascading listboxes).

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.