Form Error event

[MrNooby]

I have 2 fields required on my form (date and company). I placed Form Error event in my VB and it looks like this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
   
    Response = acDataErrContinue
    Select Case DataErr
        Case 3314
        
        If IsNull(Company) Then
          MsgBox "Enter company name", vbExclamation, "Incomplete Form"
        End If
        
        If IsNull(Date) Then
          MsgBox "Enter date", vbExclamation, "Incomplete Form"
          
        Else
         MsgBox "Nothing...", vbExclamation, "Incomplete Form"
        End If
        
        Case Else
            MsgBox "Error number " & DataErr & ". "
    End Select
   
End Sub

All OK, but I get "Nothing..." no matter if Company is empty or Date is empty... This is because "Company" or "Date" aren't really null, I checked with

...
Else
         MsgBox "Nothing..." & Podjetje"
...

And I get Nothing... OldValueOfCompanyFieldWhichWasDeleted

SO if I have company named TEST and then delete this name and save form I get "Nothing... TEST". How can I read the field with what it's in it now and not what it was before?

 

[gemma-the-husky]

an error event wont arise until something causes it to fire

this will be something like, say an attempted update failing because

a) it creates a duplicate key, in a unique key
b) some data specified as required, (and probably where a ZLS is not permitted) is not included

in your code - you are only intercepting the specific error 3314. Any other error will give you the notrmal error message. But you may not be getting an error 3314 at all.
what do you expect it to catch?

 

[ghudson]

You need to put your code in the forms before update event.

 

[MrNooby]

an error event wont arise until something causes it to fire

this will be something like, say an attempted update failing because

a) it creates a duplicate key, in a unique key
b) some data specified as required, (and probably where a ZLS is not permitted) is not included

in your code - you are only intercepting the specific error 3314. Any other error will give you the notrmal error message. But you may not be getting an error 3314 at all.
what do you expect it to catch?

I have 2 fields which are marked as required in DB (date and company).

If user doesn't write the date or name (or deletes name when editing) he should get some message which field he has to fill...

Error 3314 is correct one because it displays message (if it wouldn't be it would display other error number) but it doesn't detect field as empty even though it is.

You need to put your code in the forms before update event.

I have Private Sub Form_Error on top of everything in VB script...

If I add the code in form BeforeUpdate script like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Response = acDataErrContinue
    Select Case DataErr
        Case 3314
        
        If IsNull(Company) Then
          MsgBox "Enter company name", vbExclamation, "Incomplete Form"
        End If
        
        If IsNull(Date) Then
          MsgBox "Enter date", vbExclamation, "Incomplete Form"
          
        Else
         MsgBox "Nothing...", vbExclamation, "Incomplete Form"
        End If
        
        Case Else
            MsgBox "Error number " & DataErr & ". "
    End Select
End Sub

then I don't get any message (only defoult one...) so that can't be right.

 

[rb2006]

Hi
Just to add a bit of confusion to this

I've found that required fields sometimes need more than IsNull()

Try amending line to: If IsNull(Company) Or Trim(Company) = "" Then
etc
same with date field may work

May be worth a try to solve it

 

[ghudson]

This is all you need...

Private Sub Form_BeforeUpdate(Cancel As Integer)

        If IsNull(txtCompany) or txtCompany = "" Then
	  Cancel = True
          MsgBox "Enter company name", vbExclamation, "Incomplete Form"
        End If
        
        If IsNull(txtDate) or txtDate = "" Then
	  Cancel = True
          MsgBox "Enter date", vbExclamation, "Incomplete Form"
        End If

End Sub

* You should not use reserved words like Date to name your objects. Also, you should give your objects a prefix to identify what you are referring to in your code. Note I named the text box txtCompany for the Company text box which is bound to the Company field in your table. You should do the same.

 

[MrNooby]

Hi
Just to add a bit of confusion to this

I've found that required fields sometimes need more than IsNull()

Try amending line to: If IsNull(Company) Or Trim(Company) = "" Then
etc
same with date field may work

May be worth a try to solve it

Same thing...

The problem is that value of the field stays as it was before it was erased...

Here, for example I now have this code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
   
    Response = acDataErrContinue
    Select Case DataErr
        Case 3314
         MsgBox "Current value of Company field is: " & Podjetje
    End Select
   
End Sub

This now only fisplays valie of the field which has problems - it should be nothing since this is error for empty value but it should be written!

For example, I open record which has Company name TEST1. If I now remove TEST and try to go to some other field I get message "Current value of Company field is: TEST!". If I for example remove TEST1 and replace it with TEST2 and then remove TEST2 and move to another field
I get this message "Current value of Company field is: TEST2".

This is because value "" is not really written in database since it's marked as not null, but this should read from form Field content... I am really confused..

 

[MrNooby]

This is all you need...

Private Sub Form_BeforeUpdate(Cancel As Integer)

        If IsNull(txtCompany) or txtCompany = "" Then
      Cancel = True
          MsgBox "Enter company name", vbExclamation, "Incomplete Form"
        End If
        
        If IsNull(txtDate) or txtDate = "" Then
      Cancel = True
          MsgBox "Enter date", vbExclamation, "Incomplete Form"
        End If

End Sub

* You should not use reserved words like Date to name your objects. Also, you should give your objects a prefix to identify what you are referring to in your code. Note I named the text box txtCompany for the Company text box which is bound to the Company field in your table. You should do the same.

I have already tried to name txtCompany for error but it was the same. I tested this code which you wrote but I don't get the custom error mesage, I get:

Microsoft Office Access
You must enter a value in the 'Contacts.Company' field.

 

[SOS]

I hope you got rid of the CASE 3314 part (in the Before Update event) because there is NO ERROR when you get to the Before Update event. All you are doing there is validating that there is something filled in and then canceling the update if it doesn't pass validation. So, post the EXACT code you are trying to use now where you said you don't get a custom message or you get no error message. I think you still haven't quite gotten the code to exactly what it needs to be.

 

[MrNooby]

I have tried to add the code at Before update on the Text box of the Company and now I got the custom message.

this is how it looks like now:

Private Sub txtCompany_BeforeUpdate(Cancel As Integer)
        If IsNull(txtCompany) Or txtCompany = "" Then
      Cancel = True
          MsgBox "Enter company name", vbExclamation, "Incomplete Form"
        End If
End Sub

And so far it works, i'll also try to add for date.


Thanks for your help

 

[SOS]

However, the place to put it is in the FORM's BEFORE UPDATE event NOT the control's before update event (which is where you currently have it).

 

[MrNooby]

However, the place to put it is in the FORM's BEFORE UPDATE event NOT the control's before update event (which is where you currently have it).

If I do that this will only be good when I add new record. When I edit some record I will never get to this because error message is shown as soon as I click outside text box. So I now added this code to both, field and form before update.

It works great so far the only thing which is missing now is if I press OK I am taken to the field which is null but it's empty. If I press ESC key I get old value back. Is there any way I could do this in code? Is it possible to "send keys", just like if he would press ESC key?

 

[SOS]

If I do that this will only be good when I add new record.

No, it won't. The FORM's BEFORE UPDATE event fires WHENEVER there is something added/edited/or deleted.

It works great so far the only thing which is missing now is if I press OK I am taken to the field which is null but it's empty. If I press ESC key I get old value back. Is there any way I could do this in code? Is it possible to "send keys", just like if he would press ESC key?

Me.ControlNameHere.Undo would reverse it for the particular control if used in its before update event.

 

[ghudson]

Pressing the Esc key is the same as runnnig the Undo command. Do not use SendKeys.

You should only need the code in the forms before update event to trap for Null errors and to prevent the user from saving the record if it does not meet your criteria. Something is wrong or more is going on with the way you have it coded if my suggested code does not work since it is trapping for edits made to the record whether they are to an old record or a new record.

 

[SOS]

Something is wrong or more is going on with the way you have it coded if my suggested code does not work since it is trapping for edits made to the record whether they are to an old record or a new record.

EXACTLY, that is my view as well.

 

[MrNooby]

I tried to place the code only in form before update and it didn't work. If I remove my before update on text box I won't get custom message from form but default one (You must enter value in..." after I click on next field. I will however get custom message from form before update when i add new record and try to save it (no message is shown after leaving company field though).

Me.ControlNameHere.Undo worked, thanks.