How to add current user to table 2

[Overmyhead2]

When a record is updated on a form I want a field in the table to show current user. My database is on a server. User can be login name or the w98 workstation name.
My table has a field called operator. Is there a default value I can add to make this field update / record?
Victoria

 

[kathryn]

OK, does the database use Access Workgroup security? If so, there is a current user function.

If not, there is a Window API call that gets the current username from the login. The version I have pasted below is for Window NT, there are slightly different versions for Windows 98, etc.

*****BEGIN CODE********

Option Compare Database
Option Explicit

'-----------------------------------'First Declare this Function(API)
'-----------------------------------'Advanced Windows 32 Base API
Private Declare Function GetUserName& Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long)

Sub GetUser()

Dim strName As String
Dim lngSize As Long
Dim lReturn As Long

lngSize = 200
strName = String$(lngSize + 1, 0)
lReturn = GetUserName(strName, lngSize)
MsgBox Trim(Left$(strName, lngSize))

End Sub

****END CODE***********



The end of your question is a bit confusing; is operator the field you want to populate with the current user's name?

Kathryn

 

[Overmyhead2]

Yes, Operator is the field I want to populate. I have a field called timestamp and in the table I placed Now() in the default value property. It populates automatically. I was hoping I could use something like CurrentUser() for the default value for the Operator field and it populate automatically.
I did run the Access security wizard and added users names. I am a little unsure about how it works. It shows me as admin, not my workstation name or my network login name.
I have setup all my users' login names and W98 workstation names to be the same name and I used this name in the Access security prog. I haven't really tested it yet to see if it is going to do what I want it to do. I just want to keep my users from modifing the db design and keep track of who updated records. If I find the W98 version of the API call, where does it go? Do I place this sub as an event procedure in the control's properties?

 

[kathryn]

Did you try setting the default value of Operator to =[currentuser]? I just did a test on a secured database and the field was populated with my username.

Kathryn

 

[Overmyhead2]

Maybe my syntax is wrong. I'm using Access97.
=[currentuser]
CurrentUser()
[CurrentUser]
I've tried these but I get an error (( The database engine does not recognize either the field "CurrentUser" in a validation expression, or the default value in the table "ASPData" ))when I try to save the table.

 

[kathryn]

OK, to get into this database, do users have to login to Access? If so, you should be able to get currentuser.

If not, you are going to have to use the Windows API. Put it into a module. and call the function from the text box. I just realized that the code I posted only printed out a message box. Make sure any code you find returns the UserName.

Here is code that works in Excel:

****BEGIN CODE****

' Makes sure all variables are dimensioned in each subroutine.
Option Explicit

' Access the GetUserNameA function in advapi32.dll and
' call the function GetUserName.
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

' Main routine to Dimension variables, retrieve user name
' and display answer.
Function Get_User_Name()

' Dimension variables
Dim lpBuff As String * 25
Dim ret As Long, UserName As String

' Get the user name minus any trailing spaces found in the name.
ret = GetUserName(lpBuff, 25)
UserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)

' Display the User Name
Get_User_Name=UserName
End Sub

****END CODE******

You should be able to use this with slight modifications in Access. Then just set the default value to =Get_User_Name()

Hope that helps.






Kathryn

 

[JaneInMA]

This is very clearly described in the FAq for Access General discussion- Section 6c.
I am unsure how to get the URL without including my autologin. Elizabeth did a great job of describing this in code that easily slipped into my db with NO problems so also take a look there.

 

[Overmyhead2]

Thanks Kathryn! My users login to the db. I was using =[currentuser] in the table properties for default value for the operator field. Got the error, but when i placed the operator control on the form and set the default value to =[currentuser] there it worked perfect. Thanks again.
Victoria

 

[scking]

You realize that setting the default value of the field will only work when the record is created. Subsequent changes to the record will not indicate which currentuser made the change using this method. Try this in the form BeforeUpdate event.

[Operator] = CurrentUser

Also, you need to make sure that you are using the correct workgroup. Whatever workgroup you used when assigning usernames/passwords must be either the default workgroup OR use the /wrkgrp switch in the target property of the icon which opens the database. Do you get prompted for a username and password. If not then you are not using the correct workgroup. I suspect this to be the case when you say the system recognizes you as the Admin user, which is the default user for unsecured databases. On a secure database the Admin user must be deleted because it allows a door for users to open the database without using username/passwords.

Steve King

 

[TJones76]

Hi!
I need to do the same thing for my database as well..but I have a problem with the Users form (Admin form works fine) so I'm under my login as owner of the database and here is what I've set up:

1.) Data Entry form is based on Query with field "Data Analyst" = CurrentUser
2.) OnLoad Event: DoCmd.GoToRecord, "", acNewRecord
3.) "Data_Analyst"(field properties) DefaultValue "=[CurrentUser]"

I'm getting Error# 2105 "Can't go to specific record..etc"
Then I get a completely BLANK screen on those Users that do not have any records. For the ones that DO have existing records, I get the same Error msg, then it brings up the Form but it shows the 1st record... it should be going to a NEW record based on the code I put in the OnLoad event.

How do I correct this????

Thank you so much for your help,
Tiffany



-Tjones76