IsNull function help

[hermanko]

Hi,

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

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

 

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

 

[hermanko]

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:
http://allenbrowne.com/bug-09.html
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.

 

[hermanko]

I think that did the trick. Thanks!
Herman