before updating event calls a macro which should NOT update if checks fail

[jayArr]

I’m using the before updating event to activate a macro that runs some checks on the record which I’m about to insert into my database from a form.

IMPORTANTLY: If the checks fail I don’t want the updating to go ahead

My question is how to I achieve this?

My thoughts are to create the macro as boolean function and return FALSE if the checks fail (and hopefully halt the update) or TRUE it the checks are passed (and then allow the update to proceed).

Is this the correct way to proceed or is there better/correct alternative.

And on a more general point can this method (or the better/correct one) be used with any/all BASE form events to communicate with the underlying processes causing them to halt or proceed?

[rautamiekka]

When I were reading how to prevent a row being added in MySQL, the only way is to use a trigger to change the new row BEFORE INSERT into something that becomes illegal, such as putting NULL into NOT NULL column or a minus-sided number into non-minus-sided, meaning SIGNED number into UNSIGNED column.

[jayArr]

rautamiekka thank you for your prompt reply. Unfortunately my database is a LO BASE file & not MySQL so I’m unsure if your ‘trigger solution’ would work and anyway I need to maintain control via a macro and code so that I can alert the user how to correct the data and re-apply it.

[rautamiekka]

HSQLDB 1.8 supports triggers: Trigger (HSQLDB 1.8.0.8 API), TriggerSample (HSQLDB 1.8.0.8 API)

[jayArr]

Again unfortunately your link points to java code for triggers.
My macros are all in BASIC.
I’m after a BASIC macro solution to the problem …

[rautamiekka]

@jayArr: the code is the same for all macro languages, someone here said.

[doug]

the UNO API is the same. I would say if you can code in java with UNO then you probably can adapt Basic to suit your purposes. The reverse, however, is more difficult, IMHO.

[doug]

Test the output as you desire, and if it fails use the method .reset(). Because you are using a textbox-level macro, the result will be that the text box will revert to the old value. Here is sample code:

Sub validateTextBox (eventObj As Object)
  
  txtBox = eventObj.Source
  If txtBox.Text = "Correct" Then
    Exit Sub
  Else
    MsgBox "Validation failed; input rejected."
    txtBox.reset()
  End If
  
End Sub

I note that Before Update is triggered even when the cursor is in the textbox during a record change even if it is not modified, so there may be some fine-tuning here.

For form-level validation, you might check for thisForm.IsModified() = True and then try rolling back changes with cancelRowUpdates() or cancel(). I have had mixed success with form-level validation and rolling back form-level changes because subforms make that more complex and because of the challenges posed by validating the insert row. With a more complex form, sometimes you might not want to make the user do the whole form over again, foreign keys better.

(if this answers your question, please accept the answer by clicking the check mark () to the left)