Requiring a non-null field in a form

[Paul Ponzelli]

I would like to require a field to have a non-null value, but I need to do
this at form level instead of setting the Required property of the field to
"Yes" in the table. The reason for not wanting to do this in the table is
that not all users will be able to see this field in their forms. One
option that's available would be to move these fields into a separate table
that's related to the main table by a one-to-one relationship, and thus keep
the validation rule in the table, but I'm trying to avoid that step.

So I've tried entering these expressions in the 'Validation Rule' property
setting of the form control:

Is Not Null, and
Len(Nz([MyField]))>0

But neither of these expressions prevents a user from creating a new record
while leaving "MyField" null. I did notice, however, that if I try to
delete a value that already exists in that field, it runs the validation
error. But I need it to prevent Access from saving a new record that has
only a null value in MyField.

Is there an expression I can put in the Validation Rule property of the form
that will accomplish this?

Thanks in advance,

Paul

 

[Klatuu]

I discourage using table or field level validation for a number of reasons.
You can do this in the form's Before Update event:

If IsNull(Me.MyRequiredField) Then
MsgBox "Field Is Required"
Cancel = True
Me.MyRequiredField.SetFocus
End if

 

[Dirk Goldgar]

I would like to require a field to have a non-null value, but I need
to do this at form level instead of setting the Required property of
the field to "Yes" in the table. The reason for not wanting to do
this in the table is that not all users will be able to see this
field in their forms. One option that's available would be to move
these fields into a separate table that's related to the main table
by a one-to-one relationship, and thus keep the validation rule in
the table, but I'm trying to avoid that step.

So I've tried entering these expressions in the 'Validation Rule'
property setting of the form control:

Is Not Null, and
Len(Nz([MyField]))>0

But neither of these expressions prevents a user from creating a new
record while leaving "MyField" null. I did notice, however, that if
I try to delete a value that already exists in that field, it runs
the validation error. But I need it to prevent Access from saving a
new record that has only a null value in MyField.

Is there an expression I can put in the Validation Rule property of
the form that will accomplish this?

No, the Validation Rule will only be applied when the value of the
control is modified. Instead, use code in the form's BeforeUpdate
event; e.g.:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me!MyField) Then
Cancel = True
MsgBox "MyField is required.", vbInformation
Me!MyField.SetFocus
End If

End Sub

 

[Paul Ponzelli]

My thanks to Klatuu and Dirk -

Thanks for the straightforward solution, gentlemen.

 

[Paul Ponzelli]

Interesting question, although I wouldn't characterize the suggestion as a
"bad idea."

In fact Klatuu and Dirk provided me with a very helpful solution to a
specific need I have in my application. In that database, I have two
different groups of users who share 95% of the same fields. However, the
"required" fields for these two groups are different. If I'm going to set
the required fields at table level, I need to use two separate tables. And
while I could use union queries for the reports, they aren't updateable, so
editing existing records requires other and more elaborate arrangements. By
controlling required fields at form level, it greatly simplifies development
and maintenance.

In an ideal world, all of our databases would be designed to the umpteenth
normal form, and we would follow best practices in all components of our
design. But sometimes our real world needs are better met by departures
from the ideal.

In this case, I am very grateful that I have a way to control the required
field property at form level.

Paul

 

[Paul Ponzelli]

Why would a query for a report need to be updateable?

It doesn't - and I could use union queries for the reports. But since
they're not updateable, I wouldn't be able to use them as Record Sources for
editable forms.

If the two different groups of users were each entering their own records,
it would be a simple matter of keeping their records in two different
tables. However, those two groups share many records. One group uses all
the records, and the other group uses some of the records. In those records
used by both groups, almost all of the fields are of interest to both
groups. If I keep the data in two separate tables as you suggest, one group
has to use two different forms to enter what, from their point of view, is
essentially the same data. If I keep all the data in one table only, then
each group has to use only one set of forms.

The table (tblProcess) contains data related to Processes. It has about 30
fields, most of which are dates that occur in the process. Some of the
other fields are lookup fields into other tables such as the type of
process, the person taking action (Principal, Manager, Attorney, etc.),
benchmark events and a few calculated fields related to the status of the
process (based on the dates).

If I was dealing with a personnel database and only some of the employees
were members of the softball team, and I wanted to track their team
membership, I would set up a 1 to 1 supertype - subtype relationship. But
that's not what I have in this case. Of the 30 fields in the table, there
are only 3 or 4 fields that are not of interest to both groups of users.

When I consider how many database objects I need to create to handle one
table vs. two tables, and I also consider the navigational structure
presented to the user, it seems to me that the best solution is to use one
table, and set the "Required" validation procedures in the forms.

Paul

 

[Paul Ponzelli]

. . . yours is a bad idea.

What I do condemn, though, is the person who . . .

bad idea? Condemn?

You seem to have some knowledge about database design and Access, which we
would all welcome, Jamie. If you ease up on the negative terminology, you
might get more responses to your postings.