Default Table Value & Unknown Functions Post-Upgrade

[Mile-O]

I've finally been upgraded from Access 2003 to Access 2010. Being eleven years behind the world Access appears to have changed a great deal.

One immediate issue (aside from finding out where everything is and what's new) that I'm having is that fields where I had functions as default values are now no longer working or recognised.

So, I had the classic Dev Ashish function call for getting the username in MyField. However, it seems that functions are no longer callable

Unknown function 'fOSUserName' in validation expression or default value on 'MyTable.MyField'.

Is this correct? Or is there some setting that needs ticked? Or maybe some other hoop that Microsoft, in their infinite wisdom, require us to jump through?

I've even tried, as a fall back, using =Environ("username"), but that just confirms that functions appear not to be recognised.

 

[nanscombe]

I guess if one migrated to storing the data in Oracle, SQL Server or MySQL those functions wouldn't work so they might have removed the capability to ensure compatibility.

Personally, I would either add the fOSUserName() function as the DefaultValue of any controls that are linked to the field in the table, via a form, or add it to any Form_BeforeUpdate() events as necessary.


I had a similar jump, from Access 97 to Access 2003 back in 2006.

 

[Mile-O]

I guess if one migrated to storing the data in Oracle, SQL Server or MySQL those functions wouldn't work so they might have removed the capability to ensure compatibility.

Thanks for the response. In this instance, there's no external database connections, so no Oracle, SQL Server, or mySQL - it's purely Access.

Personally, I would either add the fOSUserName() function as the DefaultValue of any controls that are linked to the field in the table, via a form, or add it to any Form_BeforeUpdate() events as necessary.

One way, possibly, to do it. However, in some cases there is no form related data entry. There are instances where the user is importing from CSV files,and I've been tracking the date/time, and user a the time of import. Hence why the table's Default Value field was set to =fOSUserName().

I had a similar jump, from Access 97 to Access 2003 back in 2006.

While it's good to get the most recent version, it hasn't helped that it's come just before the development deadline of something I've been tasked with. I'm left flapping at new features, new layout, and message boxes telling me things no longer work, even when I'm made sure that the database format/extension remains retro.

 

[namliam]

Mile-O a blast from the long gone past... how are you ???

I recentely upgraded to 365 or 2013... Some odd things happening there too :/
They seem to have disappeared for now, hope they never return

For me the upgrade coincided with the release of SEPA which indeed threw a few wrenches in the machine... ON TOP OF undocumented SEPA features which are causing more havoc!
It has been a frigging nightmare !

On a more of a "real" answer note, the function should work, assuming you have the DLL's and the proper version 32 bit vs 64 bit...
Or possibly a missing reference, that can cause "random" functions to break

 

[Mile-O]

Mile-O a blast from the long gone past... how are you ???

I'm fine, thanks. Saw it was July 2009 since I last visited. Hope to improve on that. And yourself? I see you're catching up on me now, as regards top ten posters! (Why am I still there after five years?)

Googling around I've seen some mentions saying that user-defined functions can no longer be used in table Default Values, which is insanse, to me, although there may be good, geeky answer as to why this is now the case. Eludes me for now.

 

[nanscombe]

Again on a "real" answer note I've just fired up an old copy of Access 2003, tried to add a default value of =fOSUserName(), using a made up function, and received the error message ...

Unknown Function 'fOSUserName' in validation expression or default value on ..

However, in some cases there is no form related data entry. There are instances where the user is importing from CSV files,and I've been tracking the date/time, and user a the time of import. Hence why the table's Default Value field was set to =fOSUserName().

Run an Update query, to add the username, after the Import I guess.

 

[Mile-O]

Run an Update query, to add the username, after the Import I guess.

It's looking likely that that's going to have to be the way to do it.

From the help, emphasis added:

ERROR 3388
The function you are referencing is either unknown (because the name is invalid or misspelled) or is a type of function not allowed by the ValidationRule or DefaultValue property. Among the types of functions not allowed are user-defined, SQL aggregate functions.

 

[nanscombe]

For Access 2010 it would seem that data macros might be a way to do it ... if only there were no time constraints. :banghead:

 

[Mile-O]

Some bedtime reading there. Not just the time constraints. A staggered roll-out of Windows 7 and Office 2010...my database users are still on 2003.

 

[nanscombe]

I couldn't find anything in data macros about running a User Defined Function either so it could well be down to VBA or a query.

 

[Mile-O]

I couldn't find anything in data macros about running a User Defined Function either so it could well be down to VBA or a query.

I'll work around it. Thanks for looking.

 

[jdraw]

Can you see the function in one of your modules?
The function has to exist if it is going to work - perhaps it was not copied to 2010 specifically.

 

[nanscombe]

Can you see the function in one of your modules?
The function has to exist if it is going to work - perhaps it was copied to 2010 specifically.

For several versions of Access, the Default Value of a table field only allows for hardcoded values and some inbuilt functions, no user defined functions are allowed.

It's looking likely that that's going to have to be the way to do it.

From the help, emphasis added:

ERROR 3388
The function you are referencing is either unknown (because the name is invalid or misspelled) or is a type of function not allowed by the ValidationRule or DefaultValue property. Among the types of functions not allowed are user-defined, SQL aggregate functions.

 

[jdraw]

Thanks Nigel, I've never tried it and wasn't reading the interim posts. I thought there was a missing (uncopied) function. I now understand the 3388 error message.

If for some reason somebody wanted to change a field's defaultValue property via vba or function, then
this sort of user function could be run on database open or first form open etc.

'---------------------------------------------------------------------------------------
' Procedure : modproperty
' Author    : Jack
' Date      : 31/01/2014
' Purpose   : How to adjust default property of table via function.
'
'
'You CAN NOT use a function in the table properties directly eg  =userfunction
'It will error as follows:
'Error 3388
'
'---------------------------------------------------------------------------------------
'
Sub modproperty(tbl As String, fld As String, defval As String)

   On Error GoTo modproperty_Error

CurrentDb.TableDefs(tbl).Fields(fld).Properties("DefaultValue") = defval

   On Error GoTo 0
   Exit Sub

modproperty_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure modproperty of Module Module1"
End Sub

'---------------------------------------------------------------------------------------
' Procedure : assignFieldDefaultValue
' Author    : Jack
' Date      : 31/01/2014
' Purpose   : Sample calling routine to add/change a default value for a Table Field
'---------------------------------------------------------------------------------------
'
Sub assignFieldDefaultValue()
Dim tb  As String
Dim fl  As String
Dim dv  As String

   On Error GoTo assignFieldDefaultValue_Error

tb = "Judges"
fl = "F50"
dv = "dum mee val ue"
Call modproperty(tb, fl, dv)

   On Error GoTo 0
   Exit Sub

assignFieldDefaultValue_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure assignFieldDefaultValue of Module Module1"
End Sub

I realize this is not the same thing as was initially asked, but thought I'd try changing/assigning a default value using vba.

This link indicated default values could be adjusted this way.

I'm not sure when you would use this sort of thing.

 

[nanscombe]

If you had several people sharing a back end database wouldn't that change the Default Value for everyone each time someone logged in?

The idea of using a User Defined Function meant that everyone has a different one based to their username.

That is why it would seem that using the appropriate Form_BeforeUpdate() event(s), or an update query, would be a reasonable way to go.

Using this idea you could attribute a record to the person who created / last edited it.