Solved

How to tell if a control can accept Null

Posted on 2011-02-15
12
885 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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