Table Field Default Property Values Functions Not Working Anymore in Microsoft Access 2010

Question

I use the code below to create tables and set two field's default property values to functions. These functions set the default value to the user and date time when a record is entered. A very simple way to track when data was entered and by whom.

Apparently Microsoft ACCESS 2010 does not allow functions to be used as field default values anymore. Is there something simple (setting) I may be missing?

I understand the arguments for passing in these values directly from code and that the environ function can be manipulated. The processes that use this code are not critical enough to warrant that concern.

CurrentDb.Execute "CREATE TABLE Table(ActivityYearMonth DOUBLE, UserName TEXT, UserID TEXT, UpdatedOn DATE, UpdatedBy text)"

CurrentDb.TableDefs("Table").Fields("UpdatedOn").Properties("DefaultValue") = "=Now()"
CurrentDb.TableDefs("Table").Fields("UpdatedBy").Properties("DefaultValue") = "=Environ(""UserName"")"

What is going wrong? Code runs but default values not set? Error message (which one on which line?) — pteranodon, Jan 10 '14 at 19:43

score 3 · Answer 1 · answered Jan 10 '14 at 20:04

Access does allow some functions as field Default Value properties. But not all functions are acceptable.

"=Now()" should work. At least it does for me.

However "=Environ(""UserName"")" triggers error 3388, "Unknown function 'Environ' in validation expression or default value on 'Table.UpdatedBy'."

By default Environ is sandboxed, so Access is restrictive about where and how you can use it. Any variation of Environ will not be accepted as a field Default Value.

And this is not a new development with Access 2010. I confirmed the same behavior in Access 2007.

score 2 · Answer 2 · edited Feb 12 '14 at 01:28

2

If such functions worked before then likely the issue is sandbox mode.

http://office.microsoft.com/en-us/access-help/use-sandbox-mode-in-access-2010-HA010342092.aspx

Disabling sanbox mode and your expressions by setting SandBoxMode = 2 instead of 3 in Registry \Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines should work.

edited Feb 12 '14 at 01:28

Curtis Inderwiesche

4,940
19
60
82

answered Jan 10 '14 at 22:36

Albert D. Kallal

42,205
3
34
51

Gord Thompson · Answer 3 · 2014-01-10T22:12:57.947

1

Since you're using Access 2010 you might be able to use an event-driven data macro to achieve this. I just tried it and although =Environ(...) wasn't allowed I was able to use =GetUser() which I created as a VBA function

Option Compare Database
Option Explicit

Public Function GetUser()
    Static s As String
    Dim WshNet As Object  ' WshNetwork
    If Len(s) = 0 Then
        Set WshNet = CreateObject("WScript.Network")  ' New WshNetwork
        s = WshNet.UserName
        Set WshNet = Nothing
    End If
    GetUser = s
End Function

The Before Change data macro was simply

If [IsInsert] Then
    SetField
        Name   UpdatedOn
       Value   = =Now()
    SetField
        Name   UpdatedBy
       Value   = =GetUser()

Note: This worked fine when adding a record from within Access itself, but the GetUser() call will probably cause inserts from external applications to fail, so proceed with caution.

edited Jan 10 '14 at 22:12

answered Jan 10 '14 at 20:42

Gord Thompson

116,920
32
215
418

Consider a simpler function based on `GetUser = CreateObject("WScript.Network").UserName` If concerned about performance with late binding, include a `Static` variable to hold the `UserName` value --- then it would only need to call `CreateObject` once per Access session. – HansUp Jan 10 '14 at 20:56
1

@HansUp Good suggestions, thanks. I have tweaked the code. I still try to be a stickler about `Set objVar = Nothing` after bitter experiences with VB6 (and earlier) leaking memory. Old habits.... – Gord Thompson Jan 10 '14 at 22:15

Table Field Default Property Values Functions Not Working Anymore in Microsoft Access 2010

3 Answers3

Linked