create error message from a table restriction

[Heidi]

I am very new to programming, and am trying to figure out a way to do two
things. First: I have required all of the fields in a table to be filled in
- they cannot be null. In my form, when a user tries to skip entering a
field, I want the error message to say something like "This field cannot be
null, please enter correct info or the default value." I think the error
code is 3314, but I'm not sure where to put this error handler, or how to
envoke it for multiple fields. Second: When this error occurs I want the
form to recognize which field was left blank, and after the user hits an OK
button, I want to focus to be on that field that needs to be filled in. Any
suggestions?

 

[Klatuu]

There are a couple of places you can do this. One is at the control level.
The other is at the record level. If you want to do it at the control level,
you use the control's Before Update event. If the content of the control is
Null, prompt the user with a message box and cancel the update:

Private Sub SomeControl_BeforeUpdate(Cancel As Integer)

If IsNull(Me.SomeControl) Then
MsgBox "This Field is Required"
Cancel = True
End If

End Sub

You can use the same technique at the record level by using the form's
Before Update event and test each control:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.SomeControl) Then
MsgBox "SomeControl is a Required Field"
Cancel = True
Me.SomeControl.SetFocus
Exit Sub
End If

If IsNull(Me.AnotherControl) Then
MsgBox "AnotherControl is a Required Field"
Cancel = True
Me.AnotherControl.SetFocus
Exit Sub
End If

 

[Heidi]

Thank you for the info! I've tried it and the problem is that it seems that
because the restriction is in the table, not the form, that I get the
standard error message the table produces, so the code I put in the form
isn't tripped. Do you know how to override that?

 

[Klatuu]

You can use basically the same logic. Just put it in your error handler.

If Err.Number = 3314 Then
Select Case Screen.ActiveControl
Case "SomeField"
strMsg = "Some Field Is Required"
Case "Another Field"
strMsg = "A different Field Is Required"
End Select
MsgBox strMsg
Cancel = True
Else
'Do your regular error handling here
End If