Validating Access [Resolved by Rob]

[Danial]

Hi,
I am helping my sister with one of her college project and try to stay away from VBA. Anyhow just need some information regarding VBA in access. I have put validation in the Datbase Itself using validation rules. Now the validation works fine if I input the data directly into table. If I enter data using Forms then I am presented with a Error message saying You Can not perform this action, instead of the Error Message I set up in the Validation Rule.

I would prefer not to do the validation in the form and keep it into the Database itself. Is that possible in Access? If so how would I get that error msg defined for a particular field?

Also I have managed to setup most of the validation apart from checking for Empty value (NULL) that dont seem to work. How do i set a validation rule so User must enter something for that field.

Cheers for your help.

Danial

[RobDog888]

There are two ways to enter validation rules for Access. One is in the Table's property window
and the other is in the Form Field properties.

Are you going to allow the entry of data directly in the table or through the Form only?

Is your Form/Fields bound or unbound to the Table?

Are the data validation rules the same in both locations?

[Danial]

Rob,
Thanks for they reply.

There are two ways to enter validation rules for Access. One is in the Table's property window
and the other is in the Form Field properties.

I wasnt aware that one could enter validation rule in the Form Field itself. Cheers for that.

Are you going to allow the entry of data directly in the table or through the Form only?

As a matter of principle I always do the validation at the lowest level possible, e.g the DataBase itself. Since this is not a professional project It doesnt matter really. Almost all the data will be entered through the form rather then directly.

Is your Form/Fields bound or unbound to the Table?

All the form fields are bound to database fields.

Are the data validation rules the same in both locations?

So far all the validation rules are in the database only, where is the best place for the validation to be placed in your opinion?

Once again thank you for your help.

Cheers.

Danial

[RobDog888]

So far all the validation rules are in the database only, where is the best place for the validation to be placed in your opinion?

I gues its a matter of personal preference? I feel its best to trap data validation errors at the
business tier (but in this case its the code behind the Form?). if your going to have multiple forms
then the best place would be the table level to avoid replication of rules/code? If the fields
are bound then validation at the table level would be best.

[Danial]

Rob,
I still cant seem to figure out how to do the validation. Is it asking too much if I ask you to give me an example? Just for clarification what I need is to validate whether user has typed something using validation rule. I would prefer to set the validation rule in the database field, but if its easier for you then I dont mind if the validation is done in the form. Note that all the fields will be bound to columns.

Thanks a lot.

Danial

[RobDog888]

Sorry for the dealy. I made a single form with two textbox controls on it. Each one shows different validation criteria.

Let me know if you need more or if this doesn't help.

[Danial]

Sorry for the dealy. I made a single form with two textbox controls on it. Each one shows different validation criteria.

Let me know if you need more or if this doesn't help.

Rob,
Very much appricate for taking the time to answer my question. I was mostly stuck with how to validate empty field rather then validating input, that seem to work ok. Sorry if I was not clear. Also couldnt figure out If i could somehow display the validation message which I set in the DB itself. Any Idea?

Dont worry about it if its too much, i will just use VB code( i mean IsNull) to check instead of using Validation rule.

Thanks again.

Danial

[RobDog888]

You can use this as a validation rule to dis-allow a null value.
<>"Is Null" but for form level data entry. To dis-allow a null
entry at the table entry level you need to set Required to Yes and Allow zero length to No.

I added a table showing this for field1 and third field to the form showing the <> "IsNul".

[Danial]

You can use this as a validation rule to dis-allow a null value.
<>"Is Null" but for form level data entry. To dis-allow a null
entry at the table entry level you need to set Required to Yes and Allow zero length to No.

I added a table showing this for field1 and third field to the form showing the <> "IsNul".

Godness me! I tried everything from Not Null/ <>""/ Is Null but never thought it would be "Is Null" typed as string! Glad I dont have to do any VBA .

Thanks Rob.

Cheers for all your help.

[Danial]

Rob, Tried to PM you but said your Inbox is full. Anyhow I will try later.

[RobDog888]

Yes, VBA is a bit strange in some situations.

I have been busy on a VB.NET project lately. I cleared out a quick 50 so there is room again.