?
Solved

How to Display name of user in text box

Posted on 2011-03-01
15
Medium Priority
?
309 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
  • 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

809 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