Run-time error to trigger a Macro

[mbenton]

Hello, as users navigate through a particular form they often over look a very small detail dealing with time slots. Anyhow the over sight often generates a Run-time error '13' Type mismatch. This happens because they fail to enter a time correctly. I want the Run-time error to trigger a macro that I've created with a message of how to correct the time. I created the macro through the form's properties on "OnError". I thought to create the macro through the specific time field's properties however there is not "OnError" option for the time field, only the form has OnError option. What am I doing incorrectly? It doesn't work, the Run-time error still appears and my macro message is no where to be found. Anyone have suggestions? Is it possible to have Run-time errors trigger another message? Thanks.

[ranman256]

in the form's error trap, run the macro....

sub txtTime_AfterUpdate()
 on error goto ErrUpd

exit sub

errUpd:
if err = 13 then docmd.runmacro "mErrorMacro"
end sub

[RuralGuy]

I would think you could also catch the issue in the control's BeforeUpdate event before the error occurs.

[mbenton]

I placed the code as an Afterupdate event procedure exactly how you wrote it. I also named my macro the same as the one in your code. The Run-time error still showed but not the message in my macro. What do you mean when you say in the form's error trap?

[mbenton]

Also, if I place it in the BeforeUpdate there would be no run-time error to trigger the macro. What would trigger the macro?

[ItsMe]

Sounds like you need to trap the error on the correct event. My vote is OnExit of the control. Or use the before update and use VBA for validation instead of waiting for the err.

[mbenton]

My after update code looks like this:

Private Sub Form_AfterUpdate()
On Error GoTo errUpd
Exit Sub

errUpd:
If Err = 13 then docmd.RunMacro "mErrorMacro"
End Sub

[ItsMe]

My after update code looks like this:

Private Sub Form_AfterUpdate()
On Error GoTo errUpd
Exit Sub

errUpd:
If Err = 13 then docmd.RunMacro "mErrorMacro"
End Sub

That's fine, but I am guessing your afterupdate will never see an err 13. This is why some people are suggesting a different event. All of these events for controls fire in a certain order. If the control is set up as a Date, you are depending on the intrinsic validation to through the error. When does the form validate? Maybe the onChange would do it. I think The OnChange would produce funny results. This is why I would try OnExit (it might work for you).

Personally, I manage data validation using VBA. Maybe an unbound control would be better in this instance.

[mbenton]

The form validates when I click on a command button which runs a series of queries including a distance and time calculation. Are you suggesting that I place the code in the OnExit through that control?

[ItsMe]

The form validates when I click on a command button which runs a series of queries including a distance and time calculation. Are you suggesting that I place the code in the OnExit through that control?

No, this information is the missing link. I was wondering how Access was throwing the error and you might want to take a different approach.

Is it more than one control that is causing the error? Are the controls bound?

Before running a query, you will want to validate data in each control (whether the control is bound or not). The type mismatch is likely a null or empty where a number or date is needed.

[ItsMe]

@mbenton
It's time for me to go have wine and tapas. I can check back in on this thread tomorrow.

The bottom line is you need to make sure the correct data type is in each control before running the query(s).