function value?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a function, =user(), that captures the user's log in name. I
would like to have this set as a "default value" in the table whenever a new
record is added. I created a field in the table and placed the function,
=user(), in the table, and Access does not recognize it. I then tried to use
the expression builder and all it show are built-in-functions. Is there a way
to do this?
 
Hi, JJ.
I have created a function, =user(), that captures the user's log in name. I
would like to have this set as a "default value" in the table whenever a new
record is added. I created a field in the table and placed the function,
=user(), in the table, and Access does not recognize it.
Click to expand...

That's a table-level constraint, and table-level constraints must use
built-in functions. The common solution is to create a form and set the
bound text box control's Default Value Property to the function name.
However, since you are using a Reserved Word for your function name, you'll
have difficulties. Change the function name to something like getUser() and
make it a public function in a standard module, so that any form can retrieve
this value.

To set the Default Value Property of the text box, open the form in Design
View, open the Properties dialog window, and then select the text box bound
to the field for the user name. Select the Data tab and type the following
in the Default Value Property:

=getUser()

(Use your new function name.) Ensure that there are no spaces and that the
equals sign precedes the name of the function and a pair of open and closed
parentheses follow the name of the function. Save the form and close it.

Now whenever a new record is created with this form, the user's login name
will be assigned to the bound field for this text box.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Thanks Gunny! Works Great! One more question though....Can I have the field
recalculate when there is a change in the record. For instance, the user name
might be "JJ", but I when someone ("Gunny") changes any part of the record, I
would like the user name to recalculate to whomever is changing the record.
So, "JJ" would be changed to "Gunny" in the record.
 
Hi, JJ.

Yes. In the form's OnBeforeUpdate( ) event, assign the return value of the
function to the text box bound to the user name field. For example:

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo ErrHandler

Me!txtLastChangedBy.Value = getUser()

Exit Sub

ErrHandler:

MsgBox "Error in Form_BeforeUpdate( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

.. . . where txtLastChangedBy is the name of the text box bound to the field
that stores the user's login name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Back
Top