Required field on a form

[Orchid]

Hello All,

Hope someone can help me on my required field problems.
I have a form base on a table for users to input new Employees. There
are 4 fields that cannot be Null when entering new records. (but on
the table, I have these 4 field set up as "Required = No"). I want the
codes to be able to check if the 4 fields are null, if is null, tell
the user to input the null field(s), after the user input the required
field, then ask if want to save the new entry.

On the form's Before Update Event Procedure, I have the following code
on VB. However, after input new records, the check for null field
works, but after it tells me the field is null, it prompts if I want to
save then the form is closed and I don't have a chance to input the
required fields.

Would you please help me what codes should I use, so I can input the
info after it tells me that the field is null?

Your help is greatly appreciated!!

***********Code Begins***********************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.MARSuserID) Or Me.MARSuserID = 0 Then
Call MsgBox("You need to get the ""MARS userID"" from MARS. " _
& vbCrLf & "" _
, vbExclamation, "ENTRY REQUIRED")
Me.MARSuserID.SetFocus
'Exit Sub
End If
If IsNull(Me.SSN) Or Me.SSN = 0 Then
Call MsgBox("You need to enter the ""Social Security No."". " _
& vbCrLf & "" _
, vbExclamation, "ENTRY REQUIRED")
Me.SSN.SetFocus
'Exit Sub
End If
If IsNull(Me.Position) Or Me.Position = 0 Then
Call MsgBox("You need to select a ""PAID AS"" from the list
Provided. " _
& vbCrLf & "" _
, vbExclamation, "ENTRY REQUIRED")
Me.Position.SetFocus
'Exit Sub
End If
If IsNull(Me.DistCode) Or Me.DistCode = 0 Then
Call MsgBox("You need to select a ""Distric Code"" from the list
provided. " _
& vbCrLf & "" _
, vbExclamation, "ENTRY REQUIRED")
Me.DistCode.SetFocus
'Exit Sub
End If
If MsgBox("Do You Want To Save Your Changes?", vbYesNo + vbQuestion) =
vbNo Then
Cancel = True
Me.Undo
End If
End Sub
*****************Code Ends**************************
Orchid

[Salad]

Orchid wrote:

Hello All,

Hope someone can help me on my required field problems.
I have a form base on a table for users to input new Employees. There
are 4 fields that cannot be Null when entering new records. (but on
the table, I have these 4 field set up as "Required = No"). I want the
codes to be able to check if the 4 fields are null, if is null, tell
the user to input the null field(s), after the user input the required
field, then ask if want to save the new entry.

On the form's Before Update Event Procedure, I have the following code
on VB. However, after input new records, the check for null field
works, but after it tells me the field is null, it prompts if I want to
save then the form is closed and I don't have a chance to input the
required fields.

Would you please help me what codes should I use, so I can input the
info after it tells me that the field is null?

Your help is greatly appreciated!!

***********Code Begins***********************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.MARSuserID) Or Me.MARSuserID = 0 Then
Call MsgBox("You need to get the ""MARS userID"" from MARS. " _
& vbCrLf & "" _
, vbExclamation, "ENTRY REQUIRED")
Me.MARSuserID.SetFocus
'Exit Sub
End If
If IsNull(Me.SSN) Or Me.SSN = 0 Then
Call MsgBox("You need to enter the ""Social Security No."". " _
& vbCrLf & "" _
, vbExclamation, "ENTRY REQUIRED")
Me.SSN.SetFocus
'Exit Sub
End If
If IsNull(Me.Position) Or Me.Position = 0 Then
Call MsgBox("You need to select a ""PAID AS"" from the list
Provided. " _
& vbCrLf & "" _
, vbExclamation, "ENTRY REQUIRED")
Me.Position.SetFocus
'Exit Sub
End If
If IsNull(Me.DistCode) Or Me.DistCode = 0 Then
Call MsgBox("You need to select a ""Distric Code"" from the list
provided. " _
& vbCrLf & "" _
, vbExclamation, "ENTRY REQUIRED")
Me.DistCode.SetFocus
'Exit Sub
End If
If MsgBox("Do You Want To Save Your Changes?", vbYesNo + vbQuestion) =
vbNo Then
Cancel = True
Me.Undo
End If
End Sub
*****************Code Ends**************************
Orchid

You forgot to Cancel the operation. Ex:
If IsNull(Field) Then
Msgbox "Put some data in the Field"
Me.Field.SetFocus
Cancel = True
Endif

[Orchid]

Thanks for your reply!
I add Cancel=True, but still have the same problem. What did I miss?
Please help... Thanks!

[Salad]

Orchid wrote:

Thanks for your reply!
I add Cancel=True, but still have the same problem. What did I miss?
Please help... Thanks!

Are you closing the form when the Cancel fails? As in adding some data,
then pressing the X button on the window?

If so, it will execute your BeforeUpdate event and then continue with
closing the form. If that is the case then you might want to enter
something like this...

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2169 Then SendKeys "{Right}{Enter}"
End Sub

This is what my code may look like
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.MARSuserID) Or Me.MARSuserID = 0 Then
strMsg = "You need to get the ""MARS userID"" from MARS."
Me.MARSuserID.SetFocus
elseif IsNull(Me.SSN) Or Me.SSN = 0 Then
strMsg = "You need to enter the ""Social Security No."".
Me.SSN.SetFocus
elseIf IsNull(Me.Position) Or Me.Position = 0 Then
strMsg = "You need to select a ""PAID AS"" from the list
Provided."
Me.Position.SetFocus
elseif IsNull(Me.DistCode) Or Me.DistCode = 0 Then
strMsg = "You need to select a ""Distric Code"" from the list
provided."
Me.DistCode.SetFocus
End If

If strMsg > "" THen
msgbox strMsg,,"Entry Required"
Cancel = True
ElseIf MsgBox("Do You Want To Save Your Changes?", _
vbYesNo + vbQuestion) = vbNo Then
Cancel = True
Me.Undo
endif
End Sub