Link to home
Start Free TrialLog in
Avatar of Milewskp
MilewskpFlag for Canada

asked on

How to tell if a control can accept Null

Given a control of an open form, is it possible for VBA code to determine whether or not Null is allowed for that control?
Avatar of sjklein42
sjklein42
Flag of United States of America image

I believe you want to check the  AllowZeroLength  property on the control.
SOLUTION
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Milewskp

ASKER

Hi Helen,
<You could examine the Type property of the field >
Will the Type property tell you if the field is a required field?
"Will the Type property tell you if the field is a required field?"
No

For this you need to interrogate the Field "attributes".
Use something roughly like this:
MsgBox CurrentDb.TableDefs("YourTable").Fields("YourField").Required
Hi Jeff,
<Use something roughly like this: MsgBox CurrentDb.TableDefs("YourTable").Fields("YourField").Required>
All of my recordsources are queries. Can you tell me how to do this with queries instead of tables?
First I want to make sure we stay focused on your original question:
"How to tell if a control can accept Null"
I believe Helen has addressed this correctly.

"All of my recordsources are queries. Can you tell me how to do this with queries instead of tables?"
Queries do not have a "Required" attribute for fields. (Remember, a query is based on a table)
so you must still interrogate the source table to see if a field is Required.

Jeff
If Helen's post addressed your original Q, then please award her all the points.

Jeff
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To be fair, you never mentioned that this needed to be "Automatic" in your original post...
Again, Helen's post addresses your original Question as stated...

...(Remember my post about providing all the details up front?)

Beside your code does not really do this "Automatically" either.
It relies on an error occurring...

JeffCoachman
.
Just a comment -- I think users would be very annoyed to be constantly getting errors because controls would not accept Nulls or empty strings.  As an alternative, you could put error trapping on the form's BeforeUpdate event, to check that all the controls have appropriate values before saving the record.  Code on the Load event would not work for this purpose.