Solved

How to Display name of user in text box

Posted on 2011-03-01
15
283 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now