Full Version: track and update workstation user
UtterAccess Forums > Microsoft® Access > Access Forms
dcy
Hi,
I am using this code to get the workstation Username. This then populates into the switchboard in a text box txtUser. What I want to do is compare the returned result against a list in a table and return the proper name for the user. If the name is not in the list I want it to add it to the table.
"Get workstation username"

Option Compare Database
Option Explicit
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function UserName() As String
Dim strUsername As String ' contains Windows logon name for the current user
On Error GoTo UserName_Err
strUsername = Space(255)
If GetUserName(strUsername, (Len(strUsername) + 1)) Then
strUsername = Trim$(strUsername)
strUsername = Left(strUsername, Len(strUsername) + 1)
UserName = strUsername
Else
UserName = "errorName"
End If
UserName_Exit:
Exit Function
UserName_Err:
msgbox Err.Description
End Function
>This is the Not in list I am trying to get working.
Private Sub txtUser_AfterUpdate()
Dim strSQL As String
Dim i As Integer
Dim Msg As String
'Exit this sub if the combo box is cleared
If NewData = (select alias from tblSalesPeole where alias = UserName()) Then Exit Sub
Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = msgbox(Msg, vbQuestion + vbYesNo, "Unknown Name...")
If i = vbYes Then
strSQL = "Insert Into tblSalesPeople ([txtUser]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Any Help would be much appreciated.
Thanks
Aquadevel
This link will show you how to get the user - station name.
Workstation Name

Good luck with your project,

HAs an after thought, I normally utilize Security2000Demo
code to track the clients users as they can then move around the office and the logon name is not 'tied' to the
workstation, but to the person. Also, they don't need 'great' security, just helps to keep the honest people honest.
Edited by: Aquadevel on Tue Aug 14 16:09:26 EDT 2007.
dcy
I don't want the machine name or computer name.
That I want is the User Name. then i want to compare that against a table. If it is not in the table, add it otherwise if it is in the table get to cooresponding records.
Thanks
HiTechCoach
First thing I noticed was your "Not In List" code was in the

Private Sub txtUser_AfterUpdate()

event.

It should be in the event:

Private Sub txtUser_NotInList(NewData As String, Response As Integer)



See what happens after you move you code.
Edited by: HiTechCoach on Tue Aug 14 17:31:25 EDT 2007.
HiTechCoach
I also notice the line:

CODE
If NewData = (select alias from tblSalesPeole where alias = UserName()) Then Exit Sub


CAn SQL select will not work this way.

You will need to use a DLOOKUP() or other VBA code to lookup the value from the table.

Something like:
CODE
If NewData = DLookup("[alias]",tblSalesPeole" "alias =""" &  UserName() & """ )" Then Exit Sub



You probably don't need to even do this check. This code should only run if the value in "NewData" in not in the table which populates the combo box..
dcy
I get an error in this line when I cut and paste it
If NewData = DLookup("[alias]",tblSalesPeole" "alias =""" & UserName() & """ )" Then Exit Sub
is there an issue with the tblSalesPeople" "alias= part?
HiTechCoach
I do not think you even need this line of code. If should never match in the NotInList event.

But if you really want to have it:

Since I have not seen the table structure fior the table tblSalesPeole I do not know what the correct field would be. I would think is was a different field since you are wanting to look up "alias".


Odid have a few typos in my previous post I should of had:

CODE
If NewData = DLookup("[alias]", "tblSalesPeole",  "[alias] =""" &  UserName() & """ )" Then Exit Sub



It should be something like:

CODE
If NewData = DLookup("[alias]","tblSalesPeole",   "[Primary Key Field Name Here] =""" & UserName() & """ )" Then Exit Sub



Edited by: HiTechCoach on Tue Aug 14 23:02:34 EDT 2007.
Edited by: HiTechCoach on Tue Aug 14 23:04:25 EDT 2007.
dcy
Ok, let me expand.
blSalesPeople has 4 fields - SalesPersonID(autonumber), SalesPersonName(text), Alias(text), RoleID(number)
tblRoles = RoleID(autonumber), RoleName(txt)
There is a relationship between these two tables.
The code from above is located on a switchboard and in a text box. Right now all that is there is =UserName()
What I want to do is translate the Alias ( this is retrieved from the username of the logged on user) to the actual name from the tblSalesPeople.
I would like to keep track of this alias so that on other forms I can control access based on it.
If the user doesn't exist in the table, it needs to be added with defaults for the role. Then the table is edited to add the real user name and update the access/role.
Does this make it clearer?
I saw a post on using a hidden form to track the user. What do you think about that option.
Thanks for your great help
Lastly, can you explain to me the " usage. What constitites getting double quotes(") and so on, then maybe I can get a better handle on what these type of things should look like.
Thanks
HiTechCoach
I think the confusion has been with the terms you have used. They are for a combo box but you are using a text box.

A text box does not have this event.

In your code you have:

If NewData = (select alias from tblSalesPeole where alias = UserName()) Then Exit Sub

and

Response = acDataErrAdded

and you had not Dim'ed the variables NewData and Response whic shoudl come from the procedure definition statement like:

Private Sub txtUser_NotInList(NewData As String, Response As Integer)

It you have:

The after update event will never "fire". The code will never run in that event you are using. You will probably want to use the switchboard form's On Load event to run your code.


NOTE: I renamed the field "alias" to "UserAlias" to avoid any reserved word conflicts!

This will work:

CODE
Private Sub Form_Load()
Dim Msg As String
Dim strSQL As String
If Nz(DLookup("[Useralias]", "tblSalesPeople", "[UserAlias] =""" & UserName() & """"), "") = "" Then
    Msg = "'" & UserName() & "' is not currently in the list." & vbCrLf & vbCrLf
    Msg = Msg & "Do you want to add it?"
    
    If MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Name...") = vbYes Then
        strSQL = "Insert Into tblSalesPeople ([UserAlias]) " & "values ('" & UserName() & "');"
        CurrentDb.Execute strSQL, dbFailOnError
    End If
End If
End Sub



Also in the table tblSalesPeople , I would make sure that there is not a default value for the field RoleID if you are using referential Integrity on this field.


Edited by: HiTechCoach on Wed Aug 15 9:22:29 EDT 2007.
Edited by: HiTechCoach on Wed Aug 15 9:25:00 EDT 2007.
HiTechCoach
Also there was an issue with you UserName() code:
To get it to work I changed it to:
CODE
Option Compare Database
Option Explicit
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function UserName() As String
Dim strUsername As String ' contains Windows logon name for the current user
On Error GoTo UserName_Err
strUsername = Space(255)
If GetUserName(strUsername, (Len(strUsername) - 1)) Then
strUsername = Trim$(strUsername)
strUsername = Left(strUsername, Len(strUsername) - 1)
UserName = strUsername
Else
UserName = "errorName"
End If
UserName_Exit:
Exit Function
UserName_Err:
msgbox Err.Description
End Function
dcy
This is great code. Thank you.

Can you give me some direction if I want to track this user. ie, I want to have a way to check who they are either before I present options or when they select buttons.

Also, what is the code to change this box to be an OK button only rather than a yes/no option. I really don't want them to be able to say no.

Lastly, what is involved if the tbleSalesPeople is empty and I want to set the first user to a specific roleID and then all of the subsequent users to a different roleID.

Thanks for all of the help.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.