Required Fields in a Form

[bufbec1]

I am pretty good with Access, but do not understand VBA. I have
researched this topic and see only VBA answers, so I hope someone can
help with my specific question.

I have 2 fields for an end-user that must be filled in. I want an error
message for each field. The forms are already partially filled in, and
a user needs to select which record to go to. There are fields for them
to fill in their initials [REVIEWER] and date [REVIEW DATE], along with
other fields. It is these two fields I need to make mandatory, and need
error messages if these are not filled in. I don't know if the would be
a 'Before Exit' or 'Before Update' or which event to attach this to.
Also, if the code could place the curser into the missing field (or the
[REVIEWER] field if both are missing), that would be great.

Thanks for your help.

[Ken Ismert]

bufb...

Use the ValidationRule and ValidationText properties. For example, for
your txtReviewer control, you could use "Not Is Null" and "Enter your
initials".

This handles the case where the user enters bad data into a field. But,
what if the user skips the field altogether? To catch this, you will
need some event code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ValidateRecord = False Then
Cancel = True
End If
End Sub

Also a routine:

Private Function ValidateRecord() As Boolean

ValidateRecord = False

If IsNull(txtReviewer.Value) Then
MsgBox txtReviewer.ValidationText
txtReviewer.SetFocus
Exit Function
End If
If IsNull(txtReviewDate.Value) Then
MsgBox txtReviewDate.ValidationText
txtReviewDate.SetFocus
Exit Function
End If

ValidateRecord = True

End Function

ValidateRecord checks your required fields, and displays the
ValidationText when one is missing or incomplete, and sets focus to the
offending control. Your VBA tests in ValidateRecord will need to be the
equivalent of what you placed in ValidationRule for each control.

See Access Help on "ValidationRule" for more.

-Ken

[Dave Brydon]

<bu*****@yahoo.com> wrote :

I have 2 fields for an end-user that must be filled in. I want an error
message for each field.

I've provided two options which work for me rather well; hopefully you can
make use of one them.

Cheers,

Dave

Option One

'------------ Code Begins ------------
Private Sub Form_BeforeUpdate(Cancel As Integer)

' Ensure required fields are filled.
' Required fields, which are empty: show a customized message box to that
effect,
' and return the focus for user input.
' Check that a Category has been chosen before proceeding.

If IsNull(Me.cboCategories) Or Me.cboCategories = 0 Then
Call MsgBox("You must select a Category from the list provided, or " _
& vbCrLf & "enter a new category using the ""Edit Categories"" button
on the main menu!" _
, vbExclamation, "ENTRY REQUIRED")
Me.cboCategories.SetFocus
Exit Sub
End If

' Check that a Sub-Category has been chosen before proceeding.

If IsNull(Me.cboSubCategories) Or Me.cboSubCategories = 0 Then
Call MsgBox("You must select a Sub-Category, or choose Null from the
list provided." _
& vbCrLf & "" _
& vbCrLf & "If ''Null'' or a desired value is not available from the
list provided, you " _
& vbCrLf & "can create a new one by using the ''Edit Categories''
button, and " _
& vbCrLf & "add a new sub-category to a main category heading." _
& vbCrLf & "" _
, vbExclamation, "ENTRY REQUIRED")
Me.cboSubCategories.SetFocus
Exit Sub
End If

' Check to ensure a Question or Title has been entered prior to proceeding.

If IsNull(Me.txtQuestion) Or Me.txtQuestion = "" Then
Call MsgBox("Question is a required field!" _
& vbCrLf & "" _
& vbCrLf & "Please enter a question or title for future reference" _
& vbCrLf & "" _
, vbExclamation, "ENTRY REQUIRED")
Me.txtQuestion.SetFocus
Exit Sub
End If

End Sub

'------------ Code Ends ------------
Option Two (Note, this option does not return the user to a specific
field...modify as your see fit)

'------------ Code Begins ------------
Private Sub Form_BeforeUpdate(pintCancel As Integer)
' Check for Null values, and get confirmation each time a record is changed.

Dim strMissing As String
Dim strCRLF As String

' line feed
strCRLF = Chr(10) & Chr(13)
' 1st field value is null?
strMissing = IIf(IsNull(Me.txtSurname), "- Surname" + strCRLF, "")
' 2nd field value is null?
strMissing = strMissing & IIf(IsNull(Me.txtGivenName), "- Given Name" +
strCRLF, "")
' 3rd field value is null?
strMissing = strMissing & IIf(IsNull(Me.cboGroup), "- Group" + strCRLF,
"")
' 4th field value is null?
strMissing = strMissing & IIf(IsNull(Me.cboGrpFilter), "- Group Filter"
+ strCRLF, "")
' 5th field value is null?
strMissing = strMissing & IIf(IsNull(Me.cboElement), "- Element" +
strCRLF, "")
' 6th field value is null?
strMissing = strMissing & IIf(IsNull(Me.txtSortNo), "- Sort Number" +
strCRLF, "")
' Any required field was null?
If strMissing <> "" Then
' No update
Cancel = True
' ask user is (s)he wants to enter data or cancel and close form
If MsgBox( _
"Required information is missing: " & strCRLF & _
strCRLF & _
strMissing & _
strCRLF & _
"Do you want to enter the missing data?", vbYesNo, _
"Data missing") = vbNo Then
Me.Undo

End If

End If

End Sub
'------------ Code Ends ------------