Solved

How to tell if a control can accept Null

Posted on 2011-02-15
12
882 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 500 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now