By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,340 Members | 1,642 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,340 IT Pros & Developers. It's quick & easy.

How to automatically input the user name in a field when record is created?

P: 5
Access 2007:

I'm trying to automatically input the username of the person who created a record in a form called Data Input.
I created a Login Form which works perfectly.
In my Data Input form, in my "User" field, I wrote CurrentUser() in default value of my field. Everytime I create a record it writes Admin, even if I change user in my Login Form.
How can I get my field to take the username that was entered in my Login form?

Thank you
May 15 '14 #1
Share this Question
Share on Google+
9 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
Since Access 2007 did away with the built-in user controls, the CurrentUser() function will always return Admin. What you need to do is create your own function to pull the user name from your login form. We would need to know how you are storing this information to be able to help you write the function, however.
May 15 '14 #2

P: 5
I have a table with all the users and their passwords.
The Login form gets the information from this table.
However, I do not store the login information anywhere for now. I know there are many options but I wasn't sure how to proceed from there. You're saying that I should return the information from the login form to a new table (recording the entries of the users) in order for Access to know which user is creating the record in my form?
May 15 '14 #3

Seth Schrock
Expert 2.5K+
P: 2,951
That is one way. There is also the option of hiding the login form which allows you to reference the value on the form. You can also use temporary variables (tempvars). This last option is what I use to store the login information. There is nothing wrong with the other options though.

If you do choose to do the TempVar method, then during your login process, you would need to create the TempVar.
Expand|Select|Wrap|Line Numbers
  1. TempVars.Add "User", your login information
You could then create a function to pull this information.
Expand|Select|Wrap|Line Numbers
  1. Public Function LoginInfo()
  2. LoginInfo = TempVars("User").Value
  3. End Function
Then you can just call this function in your default value property in your form.
May 15 '14 #4

P: 5
Since I'm new to VBA, I understand the code you wrote, just don't know where to input it.
This is my OK button code in my Login Form :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command1_Click()
  2. 'Check to see if data is entered into Username
  3.  
  4.     If IsNull(Me.txtUserID) Then
  5.         MsgBox "Please enter a UserID.", vbOKOnly, "Required Data"
  6.             Me.txtUserID.SetFocus
  7.         Exit Sub
  8.     End If
  9.  
  10. 'Check to see if data is entered into Password
  11.  
  12.     If IsNull(Me.txtPassword) Then
  13.         MsgBox "You must provide a password.", vbOKOnly, "Required Data"
  14.             Me.txtPassword.SetFocus
  15.         Exit Sub
  16.     End If
  17.  
  18. 'Lookup correct password for username entered in Username
  19.  
  20.     Me.PasswordLookup.Value = DLookup("[Password]", "User Login", "[UserID] ='" & Me.txtUserID & "'")
  21.  
  22.  
  23. 'Compare value of Password to PasswordLookup and
  24.  
  25.     If Me.txtPassword.Value = Me.PasswordLookup.Value Then
  26.  
  27.  
  28.         'Close logon form
  29.  
  30.         DoCmd.Close acForm, "Login Form", acSaveYes
  31.  
  32.     Else
  33.             MsgBox "Invalid Username/Password. Please try again.", vbOKOnly, "Invalid Entry!"
  34.         Me.txtPassword.SetFocus
  35.         Exit Sub
  36.     End If
  37. End Sub
Where do I enter your code?
By the way, you are life saver!

Thank you
May 15 '14 #5

Seth Schrock
Expert 2.5K+
P: 2,951
Line 27 is where you would enter the first block of code. The second block would need entered in a regular code module (not in a form module or class module).
May 15 '14 #6

P: 5
One last question.. what would be my login information for the first block of code. I get the following error code :
Compile Error :
Wrong number of arguments or invalid property assignment.

Here is the first block of code that I entered :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command1_Click()
  2. 'Check to see if data is entered into Username
  3.  
  4.     If IsNull(Me.txtUserID) Then
  5.         MsgBox "Please enter a UserID.", vbOKOnly, "Required Data"
  6.             Me.txtUserID.SetFocus
  7.         Exit Sub
  8.     End If
  9.  
  10. 'Check to see if data is entered into Password
  11.  
  12.     If IsNull(Me.txtPassword) Then
  13.         MsgBox "You must provide a password.", vbOKOnly, "Required Data"
  14.             Me.txtPassword.SetFocus
  15.         Exit Sub
  16.     End If
  17.  
  18. 'Lookup correct password for username entered in Username
  19.  
  20.     Me.PasswordLookup.Value = DLookup("[Password]", "User Login", "[UserID] ='" & Me.txtUserID & "'")
  21.  
  22.  
  23. 'Compare value of Password to PasswordLookup and
  24.  
  25.     If Me.txtPassword.Value = Me.PasswordLookup.Value Then
  26.         TempVars.Add "User", "UserID", "Me.txtUserID"
  27.  
  28.  
  29.  
  30.         'Close logon form
  31.  
  32.         DoCmd.Close acForm, "Login Form", acSaveYes
  33.  
  34.     Else
  35.             MsgBox "Invalid Username/Password. Please try again.", vbOKOnly, "Invalid Entry!"
  36.         Me.txtPassword.SetFocus
  37.         Exit Sub
  38.     End If
  39. End Sub
  40.  
May 15 '14 #7

Seth Schrock
Expert 2.5K+
P: 2,951
Drop the "UserID" and the double quotes around the control reference so that it is just
Expand|Select|Wrap|Line Numbers
  1. Tempvars.Add "User", Me.txtUserID
That names the tempvar "User" and gives it the value that is in Me.txtUserID. You can name it whatever you want. You would just have to change the reference in your other function. If you leave the double quotes around the control reference, then it will pass that text as a literal string so TempVar("User").Value would equal Me.txtUserID instead of the value that was in the textbox.
May 15 '14 #8

P: 5
Run Time error '32538'
Can only store data and not an object.

I tried the following :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Public Function LoginInfo()
  3. LoginInfo() = TempVars.Add("User").Value
  4. End Function
Doesn't give me the runtime error but it tells me that Microsoft Access can't find my function (in my form).

I'm sorry, it's probably really easy for others...
May 15 '14 #9

Seth Schrock
Expert 2.5K+
P: 2,951
Just copy and paste what I have in the second block of code in post #4. That will fix the function.

In doing some research online, I discovered that you can't call custom functions in the Default Value property of controls. So we will just have to create the functionality of the default value in code. In the form's After Update event, add the following.
Expand|Select|Wrap|Line Numbers
  1. If Me.NewRecord Then
  2.    Me!User = LoginInfo
  3. End If
May 15 '14 #10

Post your reply

Sign in to post your reply or Sign up for a free account.