How to Display name of user in text box

I have a form called Payroll Per Month1 in MS Access. When you open the form you have to type in the Person # and select a Month year from the drop down. This will automatically populate the form with the Account, Amount and total.
 I want the form to also display the name of the user on the form which can be retrieved from the query which is attached
I also want the form to populate new data for the same user when you select a different month and year. Right now I have to start all over to accomplish that. Can you please help with that. Attached are the print screen and a copy of the table in Excel format. This is the query also for the form

SELECT DISTINCT Format([PERIOD],"mmmm yyyy") AS Expr1, DateSerial(Year([PERIOD]),Month([period]),1) AS DateFrom, DateSerial(Year([PERIOD]),Month([period])+1,1) AS DateTo
FROM [Payroll Per Month]
WHERE ((([Payroll Per Month].NAME) Is Not Null))
ORDER BY DateSerial(Year([PERIOD]),Month([period]),1);


Error1.docx
Payroll-Per-Month-Table.xlsx
Chrisjack001Asked:
Who is Participating?
 
shaydieConnect With a Mentor Commented:
No?

Did you download the database I posted? The name should be displaying in the top text box.

You wouldn't by chance have a security warning on your screen when you open the database?
That would keep it from displaying. If so you need to 'Enable the content' or open the trust center and add the location to your trusted locations.
securitywarning.jpg
0
 
hitsdoshi1Commented:
Use this function...and in the text box in the Control Source property the write

=GetUsername()

This will do the job...

Function GetUserName() As String

     Dim LUserName As String
     Const lpnLength As Integer = 255
     Dim status As Integer
     Dim lpName

     LUserName = Space$(lpnLength + 1)

     status = WNetGetUser(lpName, LUserName, lpnLength)

     If status = NoError Then
 
          LUserName = Left$(LUserName, InStr(LUserName, Chr(0)) - 1)

     Else
          MsgBox "Unable to get the name."
          End
     End If

     GetUserName = LUserName

End Function

Open in new window

0
 
hitsdoshi1Commented:
Sorry missed some part of the code.........here is the whole code. Just create the new module and paste this code.
Option Compare Database

Declare Function WNetGetUser Lib "mpr.dll" _
     Alias "WNetGetUserA" (ByVal lpName As String, _
     ByVal lpUserName As String, lpnLength As Long) As Long

Const NoError = 0

Function GetUserName() As String

     Dim LUserName As String
     Const lpnLength As Integer = 255
     Dim status As Integer
     Dim lpName

     
     LUserName = Space$(lpnLength + 1)

     
     status = WNetGetUser(lpName, LUserName, lpnLength)

   
     Debug.Print lpName
     Debug.Print LUserName
     Debug.Print lpnLength
     If status = NoError Then
 
          LUserName = Left$(LUserName, InStr(LUserName, Chr(0)) - 1)

     Else
 
          MsgBox "Unable to get the name."
          End
     End If
     Debug.Print "After"
     Debug.Print lpName
     Debug.Print LUserName
     Debug.Print lpnLength


     GetUserName = LUserName

End Function

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Just for the record, here is the de-factor standard API function to get the Windows logged in user name, to be used in the same manner as posted above.  Place this code in a standard VBA module.

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If ( lngX > 0 ) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

Reference:
http://www.mvps.org/access/api/api0008.htm

To use the function ... do as was suggested above ... calling the Function in the Control Source of a text box:

=fOSUserName()

mx
0
 
Chrisjack001Author Commented:
Does this code go in the form load. I placed =fOSUserName() in the Control source of my text box but I dont know exactly were the main code goes on the form. Please advice. Thanks for your help
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Does this code go in the form load"
No. Put that code in a standard VBA module ... name the Module vbaWhatEver ...
You have the Control Source correct.
0
 
Chrisjack001Author Commented:
What I need is the name associated with the Person_Nbr from the table not the system login. Example if the Person_Nbr is 11111 then the name that should appear on the form is Melanie Davies. The person_Nbr and the system ID are completely different  Thanks for your help.
0
 
shaydieCommented:
First off Name is a reserved word so you really shouldn't use that as a field name. If the text box is refering to a field in the underlying record source of the form just set the text box control source property to that field. If you are trying to lookup a field from another form the syntax would be

=[Forms]![Payroll Per Month Form1]![Name]
0
 
Chrisjack001Author Commented:
It did not work. This field is the Name field in the table. Each name has a person_nbr assigned and I want the name of the person to display when the Person_nbr is typed and Month/Tear selected.
0
 
shaydieCommented:
I'm pretty sure I don't understand enough about your particular form and where you are trying to get the data from...

How is it that it didn't work? What did you try and what happened?

Where are you getting your 'Name' field data from? Is it in the recordsource of the current form, a value on another form or neither?

If it is in your recordsource or on another form the above should have worked. If it is neither you may just want to lookup the value.

You could enter something like this in the after update event of the control that someone is typing the person number into. Just change your control/field/table names as appropriate:

Me.txtName = DLookup("Name", "Your Table Name", "Person_nbr=" & Me.txtEnterPerson_nbr)

0
 
Chrisjack001Author Commented:
It did not work. I probably did not do it right. I'm just a beginner in access. Attached is the database with the form, query and table that has the names where it should be getting that information from. Thanks for your help
Database11.accdb
0
 
shaydieCommented:
Great.. Not a problem. Is this what you were looking for?
Database11.accdb
0
 
Chrisjack001Author Commented:
The name is not displaying in the text box above the form as I expect it to. For example when you enter 11111 and a Month/Year the name Melanie Davies should appear in the Text box above the Person ID field on the form
0
 
Chrisjack001Author Commented:
Thanks a lot for your help. It worked
0
 
shaydieCommented:
You're welcome.. Glad to help. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.