User Required Field

[rlsublime]

I entered the following code to the before update event to require a user to enter a value in the field and then set the focus back to the field so they can try to enter the value again, but it is generating an error. Any idea what I have done wrong here? Thanks

Private Sub Loan_Number_BeforeUpdate(Cancel As Integer)
If IsNull(Me!Loan_Number) Then
    MsgBox "Loan Number is required", vbOKOnly, "Required Field Missing"
    Cancel = True
    Me.Loan_Number.SetFocus
End If
End Sub

[RuralGuy]

Cancel = True will hold the focus in the control. No need for any SetFocus code.

[RuralGuy]

BTW, You only get a BeforeUpdqate event when the user has actually *changed* the value in the control.

[rlsublime]

thanks. is there a way to get it to display the message when there is no value and not only where there is an update made? I tried adding the required field code to a save button command. It displayed a message if the field was blank but it also saved. What I am trying to do is alter the following code to do is NOT save if a required field is still blank. Any thoughts on this? Thanks

Private Sub Command199_Click()

If IsNull(Me!Loan_Number.Value) Then
          MsgBox "Please enter Loan Number"
          Cancel = True
       End If
On Error GoTo Err_Command199_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Command199_Click:
    Exit Sub
Err_Command199_Click:
    MsgBox Err.Description
    Resume Exit_Command199_Click
    
End Sub

[RuralGuy]

You can only set Cancel *IF* it is passed to you in the procedure as it is in the BeforeUpdate event. What you want to do can be done in the BeforeUpdate event of the FORM. If you don't want to save then just do Me.UnDo and it reverts the controls to what they were originally.