diazlks
Sep 9 2008, 02:28 PM
I have a text field that I want to default the current user name in my table. I use the function CurrentUser() in the Default Value on the General tab in the design of my table and I get the following message "Unknown function 'CurrentUser' in validation expression or default value". However, I can use the function CurrentUser in the default value property on my form text box and it works. Can someone help me understand why I cannot use this function on my table and give me advice on what will work to default the current user in my table?
accesshawaii
Sep 9 2008, 03:36 PM
If you're trying to set it in the table itself, "CurrentUser" will not work. You will need to set the default value to =Environ("username"), that will populate the field with the currentuser.
datAdrenaline
Sep 9 2008, 04:55 PM
>> why I cannot use this function on my table <<
To get this concept, its important to realize the Access is not a database engine. Access is a application that utilizes the JET database engine (or in A2007 the ACE database engine) to store data. So ... Access is pretty much a user interface that manipulates a database management system, and in most cases Access is manipulating the JET/ACE engine. With that in mind, CurrentUser() is an ACCESS function which returns the current user from the joined Workgroup file used to launch the Access application ... so ... the JET database engine would have no idea what that function is when it trys to evaluate a default value during the creation of a new record. The Environ() function {which returns the current Windows user on the PC} is a VBA function, and JET has this cool thing attached to it named the JET Expression Service, and the JET Expression Service is filled with VB functions ... so when JET creates a new record, it knows what Environ() is an has no problem executing it when a new record is created.
Now ... to explain why CurrentUser() works on a Form, lets first step back to what happens when a New record is created ... When a New record request is made by Access or any other application ... JET allocates some space in the database/table for this new record. This space is often called the "New Record Buffer" or "Edit Buffer". This is the stage where JET will evaluate any field level default value expressions defined in the Default Property of a field, and subsequently apply the result of the expression in the appropriate spot (or field) of the New Record Buffer ... I call this process the JET pushed default values. Now ... if you are working with data through an Access user interface object (ie: a Bound Form) and request a new record, JET will create that New Record Buffer as already mentioned, but once JET is done creating that buffer and applying the JET pushed defaults, the buffer is provide (or becomes visible) to the Access user interface object (ie: Your form), Access then does some magic and evaluates the defaults you have defined in your Controls, then Access will apply the results of those default expressions to the appropriate spot (or field) in the New Record Buffer.... I call this process the Access pushed default values, and yes, an Access pushed default will overwrite a JET pushed default. Since Access is evaluating the Access pushed defaults with expressions defined in your Form Controls, you have visibility/availability of all the Access functions (ie: CurrentUser()) and all the VBA functions (ie: Environ()) AND all the User Defined Functions you have created in your VBA code project, to use as you see fit!...
-----
Does that help?
isudady
Sep 12 2008, 03:34 PM
I have a similar issue. I have an number field (ID) that is autonumber and increments. I have another number field (Work Order) that I want to default to (ID+3000) so if the ID is 21 the Work Order would be 3021. I have some paper forms numbered from 1-3000 that I want to use up but also allow using the generated default if a paper form is not available, hence the addition of the ID and 3000.
I can't get it to work. Either I get #NAME or I get a blank. I have tried building an expression every way that I can think of.
Is the issue that there really is no value in ID at the time that Work Order is trying to get its default set?
Is there a way to do this or should I just do something else?
If someone could just give me an example of how to do this it would be great.
Thanks,
Dale
datAdrenaline
Sep 13 2008, 07:14 AM
Actually, I would just reset the "Seed Value" for the Autonumber ID ...
CODE
Public Function ChangeSeed(lngStartAt As Long, intIncrementBy As Integer)
Dim strSQL As String
strSQL = "ALTER TABLE yourtablename ALTER COLUMN yourfieldname" & _
" AUTOINCREMENT(" & lngStartAt & "," & intIncrementBy & ")"
CurrentProject.Connection.Execute strSQL
End Function
... Or ...
CODE
Public Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean
'(code orginally from the Microsoft Knowledge Base ... Note a ref to ADOX is required)
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'lngSeed = Long integer value you want to use for next AutoNumber.
Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column
'Set connection and catalog to current database.
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn
Set col = cat.Tables(strTbl).Columns(strCol)
col.Properties("Seed") = lngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("seed") = lngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing
End Function