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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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)President / OwnerCommented:
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 CoachmanMIS 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!! = ))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.