Detect if a control is empty when moving to a new record or closing a form

  • Thread starter Thread starter chuck
  • Start date Start date
C

chuck

I have a form with several text box controls. One of the text boxes has its
Control Source set to a field that requires a value. I want to be able to
detect if the user has left the text box empty when moving to the next
record or when closing the form.

If I don't do any error handling, there is a predifined error message (error
3314) that come up and gives the name of the underlying table's field name
as being empty instead of the name of the text box used in the form :

Example:
"The field 'MyTable.MyField' cannot contain a Null value because the
Required property is set to true. Enter a value in this field."

Is there a way to replace that error message with something more helpful?
Ideally, I would also want to replace 'MyTable.MyField' with the name of the
text box instead of the table's field name.

I tried using the form's OnError event which uses the following procedure:
Private Sub Form_Error(DataErr As Integer, Response As Integer).
Unfortunately, I am only successful in capturing the error number. Can I
also get the name of the field ('MyTable.MyField' ) from which I could use a
"select case" statement to correlate the name of the field to the name of
the text box?

Thanks
 
You can do something in VBA like the following when
saving the form or when exiting the text box:

Sub

On Error GoTo ErrorSection

<save of form or whatever you are doing>

ErrorSection:
If Err.Number = 3314 Then
MsgBox "The field 'Form Text Box Name' cannot
contain a Null value because the Required property is set
to true. Enter a value in this field."
Else
MsgBox Err.Number & ": " & Err.Description
End If

End Sub
 
Thanks but this is what I had tried before posting here.

If I put code in the form's OnError event, I can only capture the error
number and not the error message. The procedure that is generated looks
like this:
Private Sub Form_Error(DataErr As Integer, Response As Integer).
..my code
End sub

How can I get the error text in this procedure as well (the complete error
message that also contains the name of the field that needs a value)?

Thanks
 
I was able to achieve waht I want with this code in the form's OnError
event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

' DataErr: The error code returned by the Err object when an error occurs.
You can use the DataErr
' argument with the Error function to map the number to the
corresponding error message.

' Response: The setting determines whether or not an error message is
displayed. The Response argument
' can be one of the following intrinsic constants:
' acDataErrContinue: Ignore the error and continue without
displaying the default
' Microsoft Access error message. You can supply
a custom error
' message in place of the default error message.
' acDataErrDisplay: (Default) Display the default Microsoft Access
error message.

Response = acDataErrContinue

Select Case DataErr
Case 3314
If Nz(Me!box1) = "" Then
MsgBox "The '" + Me!box1_label.Caption + "' field must have a
value."
ElseIf Nz(Me!box2) = "" Then
MsgBox "The '" + Me!box2_label.Caption + "' field must have
a value."
Else
Response = acDataErrDisplay
End If
Case Else
MsgBox "blah blah"
End Select

End Sub
 
Back
Top