Why doesn't "not is null" work for validation on forms?

  • Thread starter Thread starter Guest
  • Start date Start date
Yes, that and <>""
Those both work, so shouldn't "Not Is Null" work too???

I am trying to understand the difference, since "not is null" seems to work
in tables and queries......
 
I have tried using validation rule
Is Not Null
and
<>""

Those work at form level, but shouldn't Not Is Null work too? If not, why?
It works on tables and queries. I want to understand the difference.
 
How are you trying to apply validation at the form level? In the form's
Before Update event?
 
Dev,

The more commonly used syntax is 'Is Not Null', whereas 'Not Is Null'
should also work.

When you set a field-level Validation Rule in the table design, it is
evaluated at the point that the record is updated. When you set the
Validation Rule on the form control, it is evaluated when the value in
the control is edited. These behaviours are different. If the 'Is Not
Null' Validation Rule is set in the table, then attempting to enter a
record on the form without entering data for the field in question, will
trigger the error. If the 'Is Not Null' Validation Rule is set on the
form control, then the error will only be triggered if you *edit that
control* such that it's value is Null, i.e. if you delete data that is
already entered there.
 
In the "data" tab on the line that specifically says "Validation Rule" and
the line below it is the text for the validation rule

Dev
 
I misunderstood. I had taken the question to mean that you wanted to apply
validation rules to the whole form at the end rather than control by
control, which is why I wondered about the Before Update event. I don't
think the validation rule is applied unless the data in the control is
changed. What happens if you enter a value, save the record (by navigating
to another record, then back, for instance), then delete what you just
added? Does the Is Not Null rule kick in?
 
oK
I have tried each of the options and am finding some "sort of" work and some
don't at all. I understand why the "don't work", as they seem to fall under
the description Steve has given below. BUT, the ones that "sort of" work
seem to not use the validation text I have provided at all.

Hmmm.....

This is for a class I am taking on Database Development at the local
community college and could still use some assistance. I believe there is
something else going on with the form that may be affecting the results of
the validation rule/text. Can I send the database to someone and see if
there is a good solution to the form? The file was created in Access 2000
format (and since modified under Access 2003) and is approx. 425kb after I
compact it!

Thanks for any assistance you may be able to provide!
 
Dev,

At this stage I am not interested in looking at your database. But if
you would care to describe the details of an example of what is
troubling you, I would be happy to advise if I can.
 
If the control starts null and remains null, that control's validation rule
will not be applied, as I understand it. It will only be applied if you
change the data in the control. Are you changing the data?
 
PurchaseID control source is tblSale.PurchaseID
Validation Rule is Is Not Null (or Not is Null, etc)
Validation Text is Please enter Purchase ID number

After entering all the other fields and leave PurchaseID blank, I would have
expected to receive my validation text message, instead it lets me leave the
field, but does not let me leave the record. Why? The error message I do
receive is

the microsoft jet database engine cannot find a record in the table
'tblPurchase' with key matching field(s) 'tblSale.PurchaseID'

Dev
 
Sorry, the piece I forgot is also when I try to change an existing record for
the PurchaseID to a null field, I get the following message

You tried to assign a Null value to a variable that is not a Variant data type


Again, many thanks
 
Dev,

Thanks for the further explanation.

No, as I mentioned before, the Validation on a form control does not
work like that. The validation check only is triggered if the control
is edited. So, if you simply "leave PurchaseID blank", you are not
editing the data in the control, and therefore the validation test will
not take place. If you want the validation test to take place in this
situation, you would be best to set the Validation Rule on the field in
the design view of the table, not on the form control.

The error message you are receiving would indicate that you have a
Relationship defined, with Referential Integrity enforced, between the
tblSale table and the tblPurchase table. The message is telling you
that you can't have a PurchaseID in the tblSale table unless there is a
corresponding entry in the PurchaseID field in the tblPurchase table. I
am not sure how this works in terms of how you have got your forms set
up, but a usual way of handling this situation is to have a form based
on the table on the "many" side of a relationship, in continuous view,
and place it as a subform on the form based on the "one" side table.
This will allow the value in the linking field to be automatically managed.
 
Dev,

This would suggest that there may be code running on the After Update
event or the Before Update event or the Exit event of the PurchaseID
control. Once again, it would be helpful if you were able to track down
the code that this error relates to, and copy/paste it here.

Still, I guess this has arisen in the process of testing to try and get
things working correctly, whereas in practice it will be very unlikely
that the user will ever manually delete a PurchaseID that has already
been entered, am I right?
 
Back
Top