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