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

[mecrazie]

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

[Seth Schrock]

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.

[mecrazie]

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?

[Seth Schrock]

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.

TempVars.Add "User", your login information

You could then create a function to pull this information.

Public Function LoginInfo()
LoginInfo = TempVars("User").Value
End Function

Then you can just call this function in your default value property in your form.

[mecrazie]

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 :

Private Sub Command1_Click()
'Check to see if data is entered into Username
 
    If IsNull(Me.txtUserID) Then
        MsgBox "Please enter a UserID.", vbOKOnly, "Required Data"
            Me.txtUserID.SetFocus
        Exit Sub
    End If
 
'Check to see if data is entered into Password
 
    If IsNull(Me.txtPassword) Then
        MsgBox "You must provide a password.", vbOKOnly, "Required Data"
            Me.txtPassword.SetFocus
        Exit Sub
    End If
 
'Lookup correct password for username entered in Username
 
    Me.PasswordLookup.Value = DLookup("[Password]", "User Login", "[UserID] ='" & Me.txtUserID & "'")
 
 
'Compare value of Password to PasswordLookup and
 
    If Me.txtPassword.Value = Me.PasswordLookup.Value Then
 
 
        'Close logon form
 
        DoCmd.Close acForm, "Login Form", acSaveYes
 
    Else
            MsgBox "Invalid Username/Password. Please try again.", vbOKOnly, "Invalid Entry!"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
End Sub

Where do I enter your code?
By the way, you are life saver!

Thank you

[Seth Schrock]

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).

[mecrazie]

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 :

Private Sub Command1_Click()
'Check to see if data is entered into Username
 
    If IsNull(Me.txtUserID) Then
        MsgBox "Please enter a UserID.", vbOKOnly, "Required Data"
            Me.txtUserID.SetFocus
        Exit Sub
    End If
 
'Check to see if data is entered into Password
 
    If IsNull(Me.txtPassword) Then
        MsgBox "You must provide a password.", vbOKOnly, "Required Data"
            Me.txtPassword.SetFocus
        Exit Sub
    End If
 
'Lookup correct password for username entered in Username
 
    Me.PasswordLookup.Value = DLookup("[Password]", "User Login", "[UserID] ='" & Me.txtUserID & "'")
 
 
'Compare value of Password to PasswordLookup and
 
    If Me.txtPassword.Value = Me.PasswordLookup.Value Then
        TempVars.Add "User", "UserID", "Me.txtUserID"
 
 
 
        'Close logon form
 
        DoCmd.Close acForm, "Login Form", acSaveYes
 
    Else
            MsgBox "Invalid Username/Password. Please try again.", vbOKOnly, "Invalid Entry!"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
End Sub
 

[Seth Schrock]

Drop the "UserID" and the double quotes around the control reference so that it is just

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.

[mecrazie]

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

I tried the following :

Option Compare Database
Public Function LoginInfo()
LoginInfo() = TempVars.Add("User").Value
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...

[Seth Schrock]

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.

If Me.NewRecord Then
   Me!User = LoginInfo
End If