?
Solved

How to tell if a control can accept Null

Posted on 2011-02-15
12
Medium Priority
?
889 Views
Last Modified: 2013-11-27
Given a control of an open form, is it possible for VBA code to determine whether or not Null is allowed for that control?
0
Comment
Question by:Milewskp
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 16

Expert Comment

by:sjklein42
ID: 34899910
I believe you want to check the  AllowZeroLength  property on the control.
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 2000 total points
ID: 34899919
It's not the control, it's the field.  You could examine the Type property of the field to which the control is bound, using the DAO Fields collection, or you could just put a certain string in the Tag property of controls bound to fields that don't allow Nulls, and have the VBA code look at the control's Tag property (the code for that would be much simpler).
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34900793
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:Milewskp
ID: 34900801
Hi Helen,
<You could examine the Type property of the field >
Will the Type property tell you if the field is a required field?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34917672
"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
0
 
LVL 1

Author Comment

by:Milewskp
ID: 34917916
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?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34918345
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34918359
If Helen's post addressed your original Q, then please award her all the points.

Jeff
0
 
LVL 1

Accepted Solution

by:
Milewskp earned 0 total points
ID: 34991644
Helen's solution will work, but it's not automatic (I manually have to enter values in the Tag property), and is only as reliable if the developer NEVER forgets to change the Tag property whenever they change the Required Property.

I prefer this idea:

Private Sub Form_Load()
   Dim MyName As String
   
   MyName = Me.Controls("Name")
   On Error Resume Next
      Me.Controls("Name") = Null
      Select Case Err.Number
         Case 0
         'Do nothing
         Case 3314
            MsgBox "Null not allowed."
         Case Else
          MsgBox "Unexpected error."
      End Select
   On Error GoTo 0
   Me.Controls("Name") = MyName

End Sub
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34994496
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
0
 
LVL 1

Author Closing Comment

by:Milewskp
ID: 35042616
.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35043641
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question