use UDF in table field's default value

hey guys,

i've got a table and one of the fields is staffID.

i've got a UDF called WindowsUserName which gets the username from windows api. (NOT the lousy environ function - only noobs do that) hahahaha

i've got a table that matches staffID with windows username.

in my default value for this staffID field, i want the.... ta da! staffID!

so i'm thinking 2 ways i can do it

1) WindowsUserName_To_StaffID(WindowsUserName)
2) Dlookup( blah blaah blah where username = WindowsUserName)

i just can't seem to figure out how to use functions in default values.

i tried =WindowsUserName but it gave me ="WindowsUserName"

tried Eval("WindowsUserName") and =Eval("WindowsUserName") but to no avail = (

hrmm i'm not sure how to do it. could yall help me guys?

thanks so sooo much!! = ))
Who is Participating?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
I suggest you set any 'Default Value' at the Form level, using the BeforeInsert Event  - which is one of the intents of that Event.

I've actually seen Default Values on the property sheet of tables get blown out.  I *never* use DF's at the Table level.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPConnect With a Mentor Commented:
i also do not use default values at the table level very often.

You should be able to use a function call to set the default value of a form control.

At the form level I regularly set default values with function calls like =GetWindowsUserName() with control's default value property.

Things to check:

First make sure the module with the function WindowsUserName is NOT also named WindowsUserName.

Second, is WindowsUserName actually declared as a function not a sub?

Depending an what you are doing, using the form's On Current and  before insert/update events may work best. I only use default values at the form level when the user can change the data. If the user is not allowed to change the data I use the Form's On Current and/or Before Update events.
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
The reason no one works at table level is because the table level rules are limited.  You can't refer to other fields and and you cannot call UDF's.

You only can call built-in VBA functions, such as Date() or Now().

Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Note that the Default Value Property of a control is really meant to be used as the default for "New Records"

So depending on when/how you are doing this, it may not work as you might think....

I am sure MX can get you sorted out here...

developingprogrammerAuthor Commented:
whao guys, those are REALLY comprehensive answers to help me understand this issue!! thanks!!

on the side note as well, yall have introduced me to Before Insert event and that is so helpful! i use it to simulate the function where the user sees a form that doesn't allow additions, then they can click an "Add Row" button where the allowadditions become true thus showing an empty row, and when they start typing, i use the before insert event to enter some information into an invisible bound control so that record is not a new record anymore and then set allowadditions to false.

i'm sure i can think of more things in the future how to use this! thanks so much once again guys!! = ))
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.