473,320 Members | 1,883 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Set a date field on unbound form to null with VBA if it has a default date value

107 Expert 100+
I'm using MS Access 2010, and I'm finding that if an unbound control on a form with a date format and a default value of any date is set to null with VBA then it reverts to the default value.

For example I use:
Expand|Select|Wrap|Line Numbers
  1. form_tmpForm.txtDateField=null
  2. debug.print form_tmpForm.txtDateField
and I get the default date.

Is there something wrong with my installation, or is that just how Access 2010 works?
Sep 30 '13 #1

✓ answered by zmbd

I am not seeing this.

Made a VERY simple form.
Bound form.
Unbound text in the details section mixed with the bound controls.
Default value set to "Default"
In the header another unbound text box with the following for the calculated control: =IIf(IsNull([Text15]),"isnull","NotNull")
Command button with the following code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub Command19_Click()
  4.     Me.Text15.Value = Null
  5. End Sub
Upon opening the form shows text15 with word "default"
The control in the header displays "NotNull"
By hand, deleted the value from text15. [Tabbed] out of text15, the control in the header changed to "isnull"
Closed the form.
Re-opened the form.
Same state upon open as before.
Used the command button to run the code.
The control in the header change value to "isnull"

Modified the command button code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub Command19_Click()
  4.     Me.Text17.SetFocus
  5.     Me.Text15.Value = Null
  6.     Me.Text15.SetFocus
  7. End Sub
Closed the form.
Re-opened the form.
Same state upon open as the first time.
Used the command button to run the code.
The control in the header change value to "isnull"
The control stayed null even after the change in focus.

Modified the command button code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub Command19_Click()
  4.     Me.Text17.SetFocus
  5.     Me.Text15.Value = ""
  6.     Me.Text15.SetFocus
  7. End Sub
Closed the form.
Re-opened the form.
Same state upon open as the first time.
Used the command button to run the code.
The control in the header change value to "NotNull"
The control stayed "NotNull" even after the change in focus.

With each test, I stepped thru a dozen records, the value in text15 remained null after being set to null or zero-length (although, with the zero-length text17 calculated to "NotNull" - there is a difference).


Running under:
OS: Win7(64)Enterprise
OFFICE: MS Office Professional Plus 2010
Version: 14.0.7106.5001(32-bit)

As this is an enterprise installation, the latest servicepak is pushed down from the servers. Therefore I would suggest that the latest updates be applied and retest the application.

3 4073
Steven Kogan
107 Expert 100+
I've Googled the topic and apparently this is how Access 2010 works with unbound controls that have a default value.
Oct 1 '13 #2
zmbd
5,501 Expert Mod 4TB
I am not seeing this.

Made a VERY simple form.
Bound form.
Unbound text in the details section mixed with the bound controls.
Default value set to "Default"
In the header another unbound text box with the following for the calculated control: =IIf(IsNull([Text15]),"isnull","NotNull")
Command button with the following code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub Command19_Click()
  4.     Me.Text15.Value = Null
  5. End Sub
Upon opening the form shows text15 with word "default"
The control in the header displays "NotNull"
By hand, deleted the value from text15. [Tabbed] out of text15, the control in the header changed to "isnull"
Closed the form.
Re-opened the form.
Same state upon open as before.
Used the command button to run the code.
The control in the header change value to "isnull"

Modified the command button code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub Command19_Click()
  4.     Me.Text17.SetFocus
  5.     Me.Text15.Value = Null
  6.     Me.Text15.SetFocus
  7. End Sub
Closed the form.
Re-opened the form.
Same state upon open as the first time.
Used the command button to run the code.
The control in the header change value to "isnull"
The control stayed null even after the change in focus.

Modified the command button code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub Command19_Click()
  4.     Me.Text17.SetFocus
  5.     Me.Text15.Value = ""
  6.     Me.Text15.SetFocus
  7. End Sub
Closed the form.
Re-opened the form.
Same state upon open as the first time.
Used the command button to run the code.
The control in the header change value to "NotNull"
The control stayed "NotNull" even after the change in focus.

With each test, I stepped thru a dozen records, the value in text15 remained null after being set to null or zero-length (although, with the zero-length text17 calculated to "NotNull" - there is a difference).


Running under:
OS: Win7(64)Enterprise
OFFICE: MS Office Professional Plus 2010
Version: 14.0.7106.5001(32-bit)

As this is an enterprise installation, the latest servicepak is pushed down from the servers. Therefore I would suggest that the latest updates be applied and retest the application.
Oct 3 '13 #3
Steven Kogan
107 Expert 100+
I guess it is because I was running 14.0.6129.5000 (32-bit).

I tried on my home PC and it works as you described, but on my work PC I can't set controls with a default value to null.

I'll look into updating the problem PC.

Thanks!
Oct 3 '13 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: S. van Beek | last post by:
Dear reader, By a Date field with Now() as default value the content of the field is date plus time. As I need a filter in a query on date only (excluding time) I invented the following...
2
by: sparks | last post by:
I am trying to find valid records in a table but I must compare a date field to a date stored in the table I can see where this is wrong (ok I guess its not text) "= """ & Me.date & """" so...
4
by: computerider | last post by:
The following code works great if there is a date for interview, but get an error 94, invalid use of null if date field on form is not entered (it is not required and should be null form time to...
3
by: Finn Stampe Mikkelsen | last post by:
Hi I have defined a table in my database, with 2 date-fields. I have set a default value to DBNull. I have integrated a nullable datetimepicker control to my project and set the apropriate...
2
by: jacc14 | last post by:
Hi Hope there is someone out there that can help. I am sure this is an easy one although not easy to explain. I have a form which produces a report using a query. On the form I have a start and...
6
by: emma82 | last post by:
Hi, I'm completely new to Access and VBA so what I'm asking is probably really simple, anyway: I have an Order form which has two date fields, one for the date the order is placed (today) and one...
3
by: Alexio | last post by:
I need to enter a date into a text field when form is loaded. However I need this date to be static when the form is opened at a later date. I have the text box set to readonly. Will this be...
5
by: ElTipo | last post by:
Hello People! I have 3 fields dates. Example: Date1, Date2 and Date3. I don't know how obtain in this 3 fields the greater date. Ex: If Date1 = 01/01/08 and Date2 = 01/02/08 and Date3 =...
3
by: David Snyder | last post by:
I have a date that is used to show when someone completed a task and the task is due again in 12,24,48 months. I am trying to update a field for the next due date. Of course after I get this done, I...
3
by: bryman | last post by:
I haven't experienced this problem of assigning Date or Now to a date field's default property so the form shows the current date. Of course, this is to help ease data entry for the user. The...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.