ACCESS 2007: Disable warnings

[bodylojohn]

Hello,

I have an unbound form that I use for searches.
On that form I have a datefield called "txtDatumVan".

I have written some code to check if the user filled in a date.
This is the following code:

Private Sub txtDatumVan_LostFocus()
'DoCmd.SetWarnings = False
If Me.txtDatumVan = "" Or IsNull(Me.txtDatumVan) Then
  Exit Sub
Else
  If IsDate(Me.txtDatumVan) Then 
  Else
    MsgBox ("Vul een datum in of kies deze uit de kalender!")
    Me.txtNaamMedewerker.SetFocus
    Me.txtDatumVan.SetFocus
  End If
End If
'DoCmd.SetWarnings = True
End Sub

This code works perfect.

However dit I adjust the property "format" of this textbox to shortdate so a datepicker appears when the tekstbox gets the focus.

Lets say I type in an "a" instead of a date I get an Office warning that I cannot input a letter but that it has to be a date.

Before I adjusted the format I got my own error message that you see in the code.

I tried docmd.setwarnings = false but without success.

What am I doing wrong???

 

[wazz]

try putting your error handler on the Form_Error event (also) and see if that helps. i think you can use response = acdataerrcontinue to stop the Access error from firing.

 

[bodylojohn]

i think you can use response = acdataerrcontinue to stop the Access error from firing.

Thanks for your reply.
Could you please give me a little example how to use the response bit?

Thanks in advance

 

[wazz]

hey there. the Response parameter is built into the event; you'll see it when you use it. it's like Cancel for the Before_Update event. for the Form_Error event, there must be a response. but you can control the response and you have, i think, three possible responses that will override the default. this one (acdataerrcontinue) will stop the default error message and continue, like 'Resume'.

first, plz add to Form_Error event only:

msgbox "form_error event is firing"

to see if the event is firing, because i'm not 100% sure this event is firing. if you see your msgbox then the event is firing. next try adding:

response = acdataerrcontinue

which might turn off the error. if all is working then add your own error handler.

 

[bodylojohn]

hey there. the Response parameter is built into the event; you'll see it when you use it. it's like cancel = true for the Before_Update event. for the Form_Error event, there must be a response. but you can control the response and you have, i think, three possible responses that will override the default. this one (acdataerrcontinue) will stop the default error message and continue, like 'Resume Next'.

first, plz add to Form_Error event only:

msgbox "form_error event is firing"

to see if the event is firing, because i'm not 100% sure this event is firing. if you see your msgbox then the event is firing. next try adding:

response = acdataerrcontinue

which might turn off the error. if all is working then add your own error handler.

Thanks again for the quick reply.

I tried this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Response = acDataErrContinue
MsgBox ("error")
End Sub

When I type in the letter "a" in my date field I only get to see the messagebox containing "error" but not the error message that I use in the lost_focus event of my Date field.

I dont get to see the office error message.

But shouldn't the be a way to use something like docmd.setwarnings = false?

 

[wazz]

you have to put your error message in with *both* events (your lost_focus error msg won't be read from the form_error error). you might want to show the form_error error again (once) just to see how it differs from the lost_focus error and adjust your error message (if necessary). you don't need to use set warnings with this one.

 

[bodylojohn]

you have to put your error message in with *both* events (your lost_focus error msg won't be read from the form_error error). you might want to show the form_error error again (once) just to see how it differs from the lost_focus error and adjust your error message (if necessary). you don't need to use set warnings with this one.

I think we are not on the same page anymor... LOL

I just want a message box to appear if a user enters anything else then a date using this code:

Private Sub txtDatumVan_LostFocus()
If Me.txtDatumVan = "" Or IsNull(Me.txtDatumVan) Then
  Exit Sub
Else
  If IsDate(Me.txtDatumVan) Then 
  Else
    MsgBox ("Vul een datum in of kies deze uit de kalender!")
    Me.txtNaamMedewerker.SetFocus
    Me.txtDatumVan.SetFocus
  End If
End If
End Sub

This code works great.

However....

When I change the format of the textbox "txtDatumVan" to shortdate and then I fill in anything else then a date I get an error message generated by access 2007.
But I just want my msgbox to appear.

I hope I explained it more clearely now.

Thanks in advance...

 

[boblarson]

Within the table you can actually set the validation rule and the validation text (text which you want displayed). Not sure if it will work with an input mask, but you can check. If you have an input mask there you might be out of luck because it will evaluate every keystroke to see if it fits correctly in the input mask and Access will fire an error itself and there's nothing you can do about it.

You mention the DoCmd.SetWarnings False, but for something of this nature you do NOT want to do that as that disables ALL system warnings for Access and you won't get anything.

 

[bodylojohn]

Within the table you can actually set the validation rule and the validation text (text which you want displayed). Not sure if it will work with an input mask, but you can check. If you have an input mask there you might be out of luck because it will evaluate every keystroke to see if it fits correctly in the input mask and Access will fire an error itself and there's nothing you can do about it.

You mention the DoCmd.SetWarnings False, but for something of this nature you do NOT want to do that as that disables ALL system warnings for Access and you won't get anything.

Thanks bob,

The thing is that I am using an unbound form and NO inputmask.
Docmd.setwarnings false doesn't work either.

What am I to do?

 

[boblarson]

Maybe you should remove the format from the text box and have it format in the BeforeUpdate event, which then you can use the code to capture the invalid entry and display the message box.

 

[bodylojohn]

Maybe you should remove the format from the text box and have it format in the BeforeUpdate event, which then you can use the code to capture the invalid entry and display the message box.

Thanks for the reply bob,

Unfortunately that doesnt work for me.
When I format the textbox on gotfocus or on before_update using this code:

Me.txtDatumVan.Format = "Short Date"

The datepicker appears when I enter the textbox.
But when I type in something that is not a date I get an error message:
"value that is not valid"

Cant I just disable the warnings for the lost_focus event of the textbox?

docmd.setwarnings false doesnt work.

 

[boblarson]

I didn't mean to use "Short Date" in that. You format it in the Before Update event with

Me.txtDatumVan = Format(Me.txtDatumVan,"mm/dd/yyyy")

or if your date format is this way dd/mm/yyyy just change the mm/dd/yyyy to that.

 

[bodylojohn]

I didn't mean to use "Short Date" in that. You format it in the Before Update event with

Me.txtDatumVan = Format(Me.txtDatumVan,"mm/dd/yyyy")

or if your date format is this way dd/mm/yyyy just change the mm/dd/yyyy to that.

Thanks again Bob,

I tried that and still I get the access error message.
I think I am just to forget about my own messagebox that tells the user that the value he entered is false en go with the Access 2007 generated error message.

If I do that at least I get the datepicker.

Thanks again for all the help guys.

 

[tinsar]

Override Input mask errors or warnings

Wazz you are a genius! Input masks have been the bane of my existance lately. They're great for controlling the input but I've never figured out how to navigate away from them without having Access pop up its stupid error which I couldn't override. Thanks for your post you've probably helped more people than you know. You definately saved me some time.

hey there. the Response parameter is built into the event; you'll see it when you use it. it's like Cancel for the Before_Update event. for the Form_Error event, there must be a response. but you can control the response and you have, i think, three possible responses that will override the default. this one (acdataerrcontinue) will stop the default error message and continue, like 'Resume'.

first, plz add to Form_Error event only:

msgbox "form_error event is firing"

to see if the event is firing, because i'm not 100% sure this event is firing. if you see your msgbox then the event is firing. next try adding:

response = acdataerrcontinue

which might turn off the error. if all is working then add your own error handler.

 

[khudsonllb]

For anyone visiting this page looking for how to turn off warnings in Access VBA, the only problem with bodylojohn's original code was in the inclusion of an equal sign that was not needed.

DoCmd.SetWarnings = False 'does not work
'DoCmd.SetWarnings False 'works fine

Hope this helps someone else.