Not able to grab user name after login to display on next form

[Alphix]

I am able to login using a single unbound box but I would like the form to recognize the user by name from the list after login.

Display the name in a designated area of the form called "Worker_Console".

I am having trouble on how to code this in.



Please help.

[ssanfu]

Welcome to the forum......


I am confused.

What do you want to store in table "tLogs"? The user_Name from the DLookup() or the user name from the Environ command?


In your insert statement, what is "Q"???

EVERY module should have these two lines at the top:

Option Compare Database
Option Explicit

The line pvUser = Environ("User_Name") should be pvUser = Environ("UserName") (no underscore)

Here is code that works:

Sub Command14_Click()
    Dim pvUser As String
    Dim User_Name As String
    Dim sSql As String



    If CLng(Me.Text3) = DLookup("[Code_Number]", "User_List", "[Code_number] = " & CLng(Me.Text3)) Then

       pvUser = Environ("UserName")

        'uncomment the next line if you want to store the user name from the table "User_list"
        'And comment out the above line that has the Environ command
        '        pvUser = DLookup("[Code_Number]", "User_List", "[Code_number] = " & CLng(Me.Text3))

        sSql = "INSERT INTO tLogs ([Event],[USER],[EntryDate]) VALUES ('" & Me.Text3 & "', '" & pvUser & "',#" & Now() & "#)"
        '       Debug.Print sSql

        CurrentDb.Execute sSql, dbFailOnError

        'user accepted..start form
        DoCmd.OpenForm "Worker_Console"
        DoCmd.Close acForm, "User_Entry"

    End If

End Sub

Read these about primary keys and autonumbers:
Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

[JeffChr]

when you open the second form from the first form, use the OpenArgs property to pass the info you want for the second form. I tried to clean this code up for you but I am not sure where you are going with this and there remain a few issues. Like take the underline character out of "User_Name" when invoking the Environ function.

[burrina]

Assuming you have a field named txtinfo, here is an example:

Me.txtinfo = Me.Worker_Console

Else to set a Caption:

Me.Caption = Me.Worker_Console.Value

HTH

[Alphix]

Thanks Steve,
I was trying to get the name of the user from the DLookup.

Can you clarify on how the Environ works? I am a newbie.

Thanks in advance (1,000 times)

[June7]

Environ() is an intrinsic VBA function. "USERNAME" is a parameter of the function. "PATH" is another. There are more but finding out what they are is not simple. http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

[Alphix]

How can I modify the code to put the user name from the table called "User_List" to table called tLogs after user logs in?

Thanks to everyone for their patience. This seems to be a hard solution to this problem.

[ssanfu]

Did you see the code in post #2?

You haven't added "Option Explicit" yet. This is important.

Here is the code again:

Sub Command14_Click()
    Dim pvUser As String
    Dim User_Name As String
    Dim sSql As String



    If CLng(Me.Text3) = DLookup("[Code_Number]", "User_List", "[Code_number] = " & CLng(Me.Text3)) Then

       '   pvUser = Environ("UserName")

        'uncomment the next line if you want to store the user name from the table "User_list"
        'And comment out the above line that has the Environ command
        pvUser = DLookup("[Code_Number]", "User_List", "[Code_number] = " & CLng(Me.Text3))

        sSql = "INSERT INTO tLogs ([Event],[USER],[EntryDate]) VALUES ('" & Me.Text3 & "', '" & pvUser & "',#" & Now() & "#)"
        '       Debug.Print sSql

        CurrentDb.Execute sSql, dbFailOnError ' <- I also corrected this. You had dbFailOn

        'user accepted..start form
        DoCmd.OpenForm "Worker_Console"
        DoCmd.Close acForm, "User_Entry"

    End If

End Sub

[JeffChr]

a little off topic but the system environment variables vary from computer to computer. they can be enumerated:

Public Function enumSEvariables()


Dim strVar As String

Dim i As Long
For i = 1 To 255
strVar = Environ$(i)
If LenB(strVar) = 0& Then Exit For
Debug.Print strVar
Next

End Function

[Alphix]

Thanks Steve for your help and guidance and especially patience.

This continue to not grab one of the user's name from the "User_List" table upon login.

The "tLogs" table collects the entry number correctly but does not grab the matching user's name from the "User_List" table.

It duplicates the entry number instead of placing: 111 in the Event column and "Jill" in the USER column and time in the EntryDate column.

here is my updated code in the attachment.


Thank you again in advance.

[June7]

Should not save names into tLog anyway. Save ID or Code_Number into User field of tLog. Make User a number type. ID is designated the PK but it is not being saved as FK. What is Code_Number for? If you don't use ID or Code_Number as PK/FK, why have them?

Do users have a last name? Name parts should be in separate fields: LastName, FirstName.

Sub Command14_Click()
    If Not IsNull(DLookup("[Code_Number]", "User_List", "[Code_number] = " & CLng(Me.Text3))) Then
        CurrentDb.Execute "INSERT INTO tLogs ([Event],[USER],[EntryDate]) VALUES ('Login', " & Me.Text3 & ", Now())", dbFailOnError
        'user accepted..start form
        DoCmd.OpenForm "Worker_Console"
        DoCmd.Close acForm, "User_Entry"
    End If
End Sub

If you want to use Environ("USERNAME") to validate the user then need field in User_List for the user's network ID. And the DLookup would be:
DLookup("Code_Number", "User_List", "NetworkID='" & Environ("USERNAME") & "'")

Where on the Worker_Console form do you want the user info?

Suggest you give controls more meaningful names.

[ranman256]

Never use code to add data (or do stuff) when you can build a simple query:

INSERT INTO tLogs ( ID, [USER] )
SELECT User_List.ID, User_List.User_Name
FROM User_List
WHERE (((User_List.User_Name)='" & getUserName() & "'));

where getUserName() function gets the users name from his/her login.
Then just execute the query
docmd.openquery "qaAddUser2Log"

'put this code into a module

Public Function getUserName()
 getUserName = CurrentUser()
End Function

[ssanfu]

@Alphix

The code is doing what you told it to do - but not what you want it to do.

However, there are things that are wrong/need to be fixed. Lets look at the code problem for now.


You have declared "pvUser" as type string.
You enter a "text number" in the text box control on form "User_Entry". (Actually, IIRC, the value entered in an unbound control is a variant)
You click the "enter" button and the code executes.

To check if the entry in the text box control (Text3) is in the table "User_List", you have used the DLookup() function.

 If CLng(Me.Text3) = DLookup("[Code_Number]", "User_List", "[Code_number] = " & CLng(Me.Text3)) Then

On the left side of the equal sign, "Text3" is converted to a long.
On the right side of the equal sign, DLookup() looks in the field "Code_Number" (type Long) in the table "User_List" where "Code_Number" is equal to what is entered into "Text3" after the value in "Text3" is converted to a long.
The values match, code continues on...

So far, so good.


Then you have

 pvUser = DLookup("[Code_Number]", "User_List", "[Code_number] = " & CLng(Me.Text3))

DLookup() arguments are exactly the same, but this time the returned value is being assigned to variable "pvUser" (type string).
"pvUser" is defined as a string, but DLookup() is returning a Long, "[Code_number]".
But what is the returned value? 111 (a Long), because that is what you told it to do.
Access allows this because Access automatically casts the Long to a string (if it can).

Look at the syntax of the DLookup() function: DLookup(Expr, Domain, Criteria)
Criteria is the "where"
Domain is the set to search: the query or table
Exp is the field to look in to match the criteria. You want "User_Name", but you are telling DLookup() to look in the "Code_Number" field.

To return the user name, you should have:

 pvUser = DLookup("[User_Name]", "User_List", "[Code_number] = " & CLng(Me.Text3))

You also need error handling code. Have you tried typing in your user name? Or anything non-numeric?
Or entered "111#"? It is easier/faster typing in a number using the numeric keypad than clicking buttons on a screen. If a user can do it, they will

Try changing the code. If you have more question or problems, post back.



You haven't described the purpose of the dB or told us anything about the process..... there are a lot of things I would do different. But that is me and my experience/style.

[Alphix]

1000 thank you's Steve.

Thank you for the detailed explanation of what the code does. I understood exactly what you were saying.

I modified the code and I was able to get it working.

Here is a brief explanation of the database I am trying to build.

I want it to be able to capture detailed time from multiple users.
The database must be flexible enough to log event types from a list.

The Worker's console must also motivate and inspire the user to perform at their best.
(This is a creative feature in which I am working on).

Once all the data is captured, I will have the ability to run selective and static queries upon
the user's desires.

I am a newbie but eager to learn during this development stage.

Thanks again....