Solved

How to Display name of user in text box

Posted on 2011-03-01
15
298 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Chrisjack001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
15 Comments
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35012885
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
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 35012971
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
 
LVL 75
ID: 35014543
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Chrisjack001
ID: 35016522
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
 
LVL 75
ID: 35017938
"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
 

Author Comment

by:Chrisjack001
ID: 35029467
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
 
LVL 7

Expert Comment

by:shaydie
ID: 35030839
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
 

Author Comment

by:Chrisjack001
ID: 35032216
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
 
LVL 7

Expert Comment

by:shaydie
ID: 35032747
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
 

Author Comment

by:Chrisjack001
ID: 35034095
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
 
LVL 7

Expert Comment

by:shaydie
ID: 35037477
Great.. Not a problem. Is this what you were looking for?
Database11.accdb
0
 

Author Comment

by:Chrisjack001
ID: 35038079
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
 
LVL 7

Accepted Solution

by:
shaydie earned 500 total points
ID: 35039272
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
 

Author Closing Comment

by:Chrisjack001
ID: 35039525
Thanks a lot for your help. It worked
0
 
LVL 7

Expert Comment

by:shaydie
ID: 35039859
You're welcome.. Glad to help. :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question