Using Validation Rule on form

[Jay Kay]

I need to force the users entering data on a form to enter data in all the
fields. I can’t set the fields to be required at the table level because I’m
appending records into the table on a daily basis (import from excel) with
most of the fields missing.

Is it possible to set the fields to be required in the form without having
to code anything, but use the properties of each field? (I don’t have the
knowledge to write any code).

I tried using the Validation Rule on each control but I’m not doing it
correctly since I can save each record with any/all of the required fields
being blank.

Here are a couple of examples
text box =len([lastname]&"")>0
combo box =[category]>0

Should I be checking for the expression to be true or false? For example,
for the category not be be blank, do I check that it's not 0 or that it is 0?

Can someone help explain the syntax I’m supposed to be using and provide an
example for a date and memo field too please?

Thanks in advance for your assistance.

 

[Klatuu]

The easiest way is to use the Before Update event of the form. There are
methods that take less code, but to get you started, you need to check for a
value in each control. If it has no value or an invalid value, set Cancel =
True and the form will not update the record. Since your data is coming from
Excel, you can't be sure that the value will be Null. It may be an empty
string, depending on what the user entered in the spreadsheet. So, you have
to check for either Null or an empty string. It would be something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Nz(Me.FirstControl, vbNullString) = vbNullString Then
MsgBox "Enter Valid Value For FirstControl", vbExclamation
Cancel = True
Exit Sub
End If

If Nz(Me.NexttControl, vbNullString) = vbNullString Then
MsgBox "Enter Valid Value For NextControl", vbExclamation
Cancel = True
Exit Sub
End If

.... And so on for all the controls you want to validate.
End Sub

 

[Jay Kay]

Thanks for the response. I'll give it a try.

The easiest way is to use the Before Update event of the form. There are
methods that take less code, but to get you started, you need to check for a
value in each control. If it has no value or an invalid value, set Cancel =
True and the form will not update the record. Since your data is coming from
Excel, you can't be sure that the value will be Null. It may be an empty
string, depending on what the user entered in the spreadsheet. So, you have
to check for either Null or an empty string. It would be something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Nz(Me.FirstControl, vbNullString) = vbNullString Then
MsgBox "Enter Valid Value For FirstControl", vbExclamation
Cancel = True
Exit Sub
End If

If Nz(Me.NexttControl, vbNullString) = vbNullString Then
MsgBox "Enter Valid Value For NextControl", vbExclamation
Cancel = True
Exit Sub
End If

... And so on for all the controls you want to validate.
End Sub
--
Dave Hargis, Microsoft Access MVP

I need to force the users entering data on a form to enter data in all the
fields. I can’t set the fields to be required at the table level because I’m
appending records into the table on a daily basis (import from excel) with
most of the fields missing.

Is it possible to set the fields to be required in the form without having
to code anything, but use the properties of each field? (I don’t have the
knowledge to write any code).

I tried using the Validation Rule on each control but I’m not doing it
correctly since I can save each record with any/all of the required fields
being blank.

Here are a couple of examples
text box =len([lastname]&"")>0
combo box =[category]>0

Should I be checking for the expression to be true or false? For example,
for the category not be be blank, do I check that it's not 0 or that it is 0?

Can someone help explain the syntax I’m supposed to be using and provide an
example for a date and memo field too please?

Thanks in advance for your assistance.

 

[John W. Vinson]

I need to force the users entering data on a form to enter data in all the
fields. I can’t set the fields to be required at the table level because I’m
appending records into the table on a daily basis (import from excel) with
most of the fields missing.

Is it possible to set the fields to be required in the form without having
to code anything, but use the properties of each field? (I don’t have the
knowledge to write any code).

I tried using the Validation Rule on each control but I’m not doing it
correctly since I can save each record with any/all of the required fields
being blank.

Here are a couple of examples
text box =len([lastname]&"")>0
combo box =[category]>0

Should I be checking for the expression to be true or false? For example,
for the category not be be blank, do I check that it's not 0 or that it is 0?

Can someone help explain the syntax I’m supposed to be using and provide an
example for a date and memo field too please?

Thanks in advance for your assistance.

You cannot use code or macros on individual controls, since you have no
assurance that the user will even touch a control once they open the form. If
they open the form, change one value, and then close it, none of the other
controls' events will ever fire at all.

You must instead use the Form's BeforeUpdate event to check the required
controls.

Here's some code you should be able to adapt:

Private Sub Form_BeforeUpdate(Cancel as Integer) <<< Access gives you this
Dim strProblem As String
Dim iAns As Integer
strProblem = ""
If Me![LastName] & "" = "" Then
strProblem = strProblem & "Last Name , "
End If
If Me![FirstName] & "" = "" Then
strProblem = strProblem & "First Name, "
End If
If Me!Category & "" = "" Then
strProblem = strProblem & "Category, "
End If
<etc through all the required field/control names>
If strProblem <> "" Then ' Were any problem fields found?
strProblem = Left(strProblem, Len(strProblem - 2) ' trim off trailing comma
iAns = MsgBox("Please fill in " & strProblem, vbOKCancel)
Cancel = True
If iAns = Cancel Then
Me.Undo ' If user clicks the Cancel button undo all changes
End If
End If
End Sub

This will pop up a message box saying

Please fill in Last Name, First Name

if those controls were left null or blank, and cancel the table update; the
user can click OK to fill in those fields, or Cancel to start over as if
they'd never opened this record.

John W. Vinson [MVP]