Table Field Default Property Values Functions Not

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"")"

Answer 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.

Answer 2

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.

Answer 3

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.