[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to tell if a control can accept Null

Posted on 2011-02-15
12
Medium Priority
?
887 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

649 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