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

 

[Guest]

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.

 

[onedaywhen]

I discourage using table or field level validation for a number of reasons.

Care to post your reasons? Are they as good as these reasons as to why
yours is a bad idea?

http://www.dbazine.com/ofinterest/oi-articles/celko25/

"you mop the floor and fix the leak. The puddle is the application side
and the leak is the database side. The database will be the final,
trusted authority since it will reject rows that violate constraints."

Jamie.

--

 

[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

 

[onedaywhen]

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.

Yes, that sounds like the correct approach. I assume the different
types of user determines distinct entity types (e.g. a manager's
employee benefit is distinct from a non-manager's employee benefit). If
the entities are distinct then they probably should be in different
tables.

while I could use union queries for the reports, they aren't updateable

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

editing existing records requires other and more elaborate arrangements.

In the same way you have one table for each of the two distinct entity
types, you could have two different views (queries), two different
procedures for updating the rows, etc and your front end will call the
view/proc/whatever appropriate to the type of user/entity.

I wouldn't characterize the [replier's] suggestion as a
"bad idea."

OK, talking another view now. Let's assume you've investigated the
options and found that limitations of the Access/Jet DBMS engine mean
you must temporarily compromise your design for practical reasons until
they enhance the engine (you may be better advised to find a platform
that is being more actively enhanced). In which case, I would commend
you for that.

What I do condemn, though, is the person who seems to recommend that no
one should ever use domain checking at the database level by saying, "I
discourage using table or field level validation." If they are speaking
generally, as they appear to be, then I think that is bad advice. If
they intended to refer to your particular case, it would be helpful if
they post back with their reasons.

Jamie.

--

 

[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.

 

[onedaywhen]

bad idea? Condemn?

ease up on the negative terminology

Help me here. I see something which is plain and simple bad advice.
What am I supposed to say? Use this as an example:

"Never use validation on the database side. I have my reasons for
saying this but I am not prepared to reveal them, even when challenged
to do so."

Paul, mine is not a rhetorical question, I want to see you post an
example response which you feel is diplomatic yet strong enough to
clearly convey that you think the advice given is borderline dangerous
to the general public (that's genuinely how I feel in this case).

I look forward to your response and promise to try harder next time.

Jamie.

--