IsNull function help




I have a form, which contains a subform. Both the form and subform
display different query results... both originating from the same

upon clicking a cmd button, I would like, thru code, to check that a
particular field, call it [LastName] is NOT null in any records. That
is, if the user has accidentally deleted any info in the [LastName]
field and clicks that button, a msgbox should display saying that field
cannot be null.

Currently I have this:
If IsNull(Me![LastName]) Or IsNull(Me![Version
History].Form.[LastName]) Then

where [Version History] is my subform control name.

The above line doesn't always work because it seems when i test it by
deleting info in the [LastName] field for one record, then put the
cursor in another record, then click the button, it seems to only check
that the current record with focus is OK (i.e. not NULL) and so no msg
appears even tho the record i just moved off of now has a null
[LastName] field.

I hope my problem was explained clearly. Hope someone can help me out.

Allen Browne

Access saves each record separately. If you want to ensure that the data
cannot be saved if a particular field is left blank you *must* test this on
each single record before it is saved. Testing it all later is too late: the
data is already in the table.

The simplest method (no code needed) is to:
1. Open your table in design view.
2. Select the field that must have a value.
3. In the lower pane of table design set the Required property to Yes.
Now Access won't let you save the record without filling in that field.

If you want to do it in code, use the BeforeUpdate event of the *form*.

If you want to go back and check other existing records (not just the one
that is being entered), use a query.


Hi Allen,
Thanks for your response. I tried to set the property Required to Yes
for the field in question in my table design view.

However, there are actually several fields where i want to "force" a
field to be filled in. I set each of them to Yes and tested it. Only
one of the fields works (i.e. displays an error), while i am still able
to delete the text from the other fields and still save the table. Is
there any reason for this behaviour?

Allen Browne

If these are Text fields, make sure you set their Allow Zero Length property
to No as well.

That property should be No for almost every text field in your database
anyway. This article:
includes a FixZLS() function that sets this property to No for all your
fields in all your tables.

If Access still allows you to save a record with a null in a required field
after that, something else is wrong.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question