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

[Steven Kogan]

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:

form_tmpForm.txtDateField=null
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?

[Steven Kogan]

I've Googled the topic and apparently this is how Access 2010 works with unbound controls that have a default value.

[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","Not Null")
Command button with the following code:

Option Compare Database
Option Explicit
Private Sub Command19_Click()
    Me.Text15.Value = Null
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:

Option Compare Database
Option Explicit
Private Sub Command19_Click()
    Me.Text17.SetFocus
    Me.Text15.Value = Null
    Me.Text15.SetFocus
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:

Option Compare Database
Option Explicit
Private Sub Command19_Click()
    Me.Text17.SetFocus
    Me.Text15.Value = ""
    Me.Text15.SetFocus
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)Enterpr ise
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.

[Steven Kogan]

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!