vallidation rule on table stops macro on a form.

[Bdaviskar]

Hello.
Short version-
I have a validation rule at the table level that stops a macro on a command button on a form that sets a few tempvars and goes to a new record. Since the validation rule gives me the pop up with the validation text explanation it stops the macro and the user sees the macro single step error box. I can’t seem to set the order on the macro including an on error function to fix this. Can anyone help me set up this macro to cancel after the validation rule stops the macro?
Long Version-
I have a form that is based on a table. The table has a before change macro (lookup record for duplicates records) and several text boxes with a IS Not Null validation rule. Everything works correct if I use the provided next button at the bottom of the page. If the user forgets to input a text box with the validation rule, then I get the pop up with the validation text. The user can now continue.
However I have a macro based off a command button that basically sets a few tempvars- go to new record and fills in 2 other text boxes. If everything is correct it works great but if there is a field where there is a null value and I have a validation rule, I get the correct validation text pop up but then the Macro stops and I get the single step macro box pop up. I do not want the user to see this.
I have tried to using the on error function first but then I never get the validation text to tell the user what is wrong. I have tried many combinations in the macro but I cannot seem get it correct.
I am trying to fix the macro to show me the validation text from the table level and cancel the macro.
Any help is REALLY APPRECIATED
I did try to do a search but I could not find anything.
Thank you

 

[Mihail]

If everything is correct it works great but if there is a field where there is a null value and I have a validation rule, I get the correct validation text pop up but then the Macro stops and I get the single step macro box pop up

So, you know very well when this happen. Why you don't handle this ?

If CertainField = Null (you know what fields cause the problem)
MsgBox.......
GoTo PointToContinue

 

[Bdaviskar]

Thank you for answering my post. With your post and some more figuring out I was able to fix my macro on my form. I am not sure if I did it the way you suggest. I am not sure what you meant by goto pointtoContinue. Could you explain a little further?

Here is what I did - In Red was the added sections that worked.

ECHO [NO]
DisplayHourglass [Yes]
OnError [MacroName, Error]
Save record
SetLocalVar
GotoRecoerd [New]
SetProperty [Text box, value = LocalVAr!VAr]
SubMAcro Error
OnError [Next]
If [MAcroError} <>0 Then
Messsage Box (= [MacroError] .[Description],Beep,Yes)
End If
This allowed my Macro to continue and show the Explanation of what text boxes the user left null. (Message was the Validation Text)

Thank you again.

 

[Mihail]

Glad to help you.

I tried to use macros but I quickly understand that VBA is a lot more flexible. So I have no experience with macros.
In this forum there are more posters that speak about "macro" but they need, in fact, VBA.

So, my post is based on VBA. In VBA you can define a label in order to jump, in code, to that label. Something like this:

If [COLOR=Blue]condition [/COLOR]Then
  GoTo [B]PointToContinue [/B]'Jump to this label
End If
'Here is code that will be executed if [COLOR=Blue]condition [/COLOR]= False

[B]PointToContinue[/B]: 'the label
'This code will be executed. The execution not depend from the [COLOR=Blue]condition [/COLOR]value

Of course that the previous code can be replaced with a structure If-Then-Else but the point is that the label PointToContinue can be anywhere in the same procedure: after or before the GoTo statement.

As far as I can understand from your macro, you founded a way to raise a error.
If Save record is OK then no error. But, if is NOT OK a error is raised and the On Error statement catch and handle this error.

Unfortunately, this can become a trap for you.
The Save record statement can fail because other (unknown) reasons. This error wil be treat as well as the known one.