Solved

How to tell if a control can accept Null

Posted on 2011-02-15
12
880 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

758 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

18 Experts available now in Live!

Get 1:1 Help Now