IS NOT NULL not working in control's validation rule

[lodhi1978]

Hi,

I am using Access 2010 on Windows 7 (64-bit).
I have create a table clients that contains multiple columsn i.e. Id, First Name, Last Name, Starting Date, Company , while defining the table I did not mark REQUIRED property of any column

I have created a form based on this table "CLIENTS",
I create the company as combo box and taking the list from COMPANIES.ID
I put the validation rule as IS NOT NULL and put validating text Select Company from list.
If I marked REQUIRED property of this column in table definition as YES then it displays system generated message with tablename.controlname, while i want to display my own message.

But when i input the data and leave the Company column blank the validation is not executed.

There are three columsn in the form on which I want to apply the same validation.

Kindly advice.

Thans,

Hassan

 

[CJ_London]

'is not null' is a sql term, what you need to use is

not isnull(comboboxname)

However I think you are going about this the wrong way.

I presume you are trying to ensure that a user cannot leave the form control blank - in which case just set the fields required property to yes - as you have tried.

If you don't want to use the system message then you need to create you own in the form before update event. The code will be something like

if isnull(combobox1) then
    msgbox "my message"
    cancel=true
elseif isnull(combobox2) then
    msgbox "my 2nd message"
    cancel=true
elseif isnull(combobox3) then
    msgbox "my 3rd message"
    cancel=true
end if

 

[lodhi1978]

Thanks CJ,

You are right, I dont want user to left some fields blanks, and also wanted to prompt him immediately if he left the field blank.
I tried Not isNull in validation rule but it is not working, I also wrote the suggested code on "On Update" event but it was also not working,
I then write this code on Save button and its working fine here.

but is there any way to prompt user immediately, i.e. when he left the requried field blank prompt must be appear.

Thanks

Hassan

 

[pr2-eugin]

I never use IsNull to validate, I use Len() function. This will validate both Null and Null Strings.

IsNull(vbNullString) - False
IsNull(Null) - True
Len(vbNullString) - 0
Len(Null) - Error

So, I use

If Len(someControl & vbNullString) = 0 Then _
    MsgBox "Field cannot be left blank"

 

[CJ_London]

'not working' is not very helpful - it probably wasn't workig becuase I said to put it in the before update event and you've put it in the 'on update' event which I've not heard of.

you can put the relevant code for each combo in each controls lostfocus event but this will only be triggered if the user goes to that control in the first place, which they may not do.