Ask Your Question
0

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

asked 2015-08-14 18:31:19 +0200

jay Arr gravatar image

updated 2015-08-14 18:33:06 +0200

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?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2015-08-15 04:56:42 +0200

doug gravatar image

updated 2015-08-15 05:02:06 +0200

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 (image description) to the left)

edit flag offensive delete link more
0

answered 2015-08-14 18:48:43 +0200

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.

edit flag offensive delete link more

Comments

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.

jay Arr gravatar imagejay Arr ( 2015-08-14 19:16:21 +0200 )edit
rautamiekka gravatar imagerautamiekka ( 2015-08-14 19:22:50 +0200 )edit

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 ...

jay Arr gravatar imagejay Arr ( 2015-08-14 23:03:39 +0200 )edit

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

rautamiekka gravatar imagerautamiekka ( 2015-08-15 00:49:41 +0200 )edit

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 gravatar imagedoug ( 2015-08-15 04:58:46 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-08-14 18:31:19 +0200

Seen: 405 times

Last updated: Aug 15 '15