asked on

GetUserName "Undefined funtion GetUserName() in expression"

I have an Access 2007 application. Environ("Username") wasn't working on some user machines, so I have been trying to implement the GetUserName function. I pasted the code provided into a module. But when I try to use the function, I get the error "undefined function GetUserName() in expression".

My specific use is: On a form, when the user clicks the 'approve' button, I need to set the value of the [AuditUser] field to the user name (plus the date, but that works fine). Here's the SQL:

UPDATE AuditLog SET AuditLog.AuditUser = GetUserName(), AuditLog.AuditDate = Date()
WHERE (((AuditLog.Log_ID)=[Forms]![Member Main Form]![ACH subform].[Form]![AuditLog subform].[Form]![Log_ID]));

This worked fine using Environ("Username") on some machines, but doesn't work using the GetUserName() function.

I'm sure this is easy, but my coding experience is minimal, so I'm stuck. thanks for any help.

Chuck Wood

What operating system is on the machines where Environ("USERNAME") does not work?
-Chuck

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Chuck Wood

Did you declare the GetUserName function at the top of the module?

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA"(ByVal lpBuffer As String, nSize As Long) As Long

-Chuck

mkwest

ASKER

Yes. Here is my module:

Option Compare Database
Option Explicit

Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetComputerName() As String
On Error GoTo Err_GetComputerName
Dim Username As String * 255
Call GetComputerNameA(Username, 255)
GetComputerName = Left$(Username, InStr(Username, Chr$(0)) - 1)
gCmpNm = Left(GetComputerName, 4)
'Forms.frmFindInvoices.txtComputerName = gCmpNm
Exit_GetComputerName:
Exit Function

Err_GetComputerName:
MsgBox Err.Description
Resume Exit_GetComputerName

End Function

Public Function GetCurrentUserName() As String
On Error GoTo Err_GetCurrentUserName
Dim lpBuff As String * 255
Dim ret As Long, Username As String
ret = GetUserName(lpBuff, 255)
Username = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
GetCurrentUserName = Username & ""
gUsrNm = GetCurrentUserName
Forms.frmFindInvoices.txtUsername = gUsrNm
Exit_GetCurrentUserName:
Exit Function

Err_GetCurrentUserName:
MsgBox Err.Description
Resume Exit_GetCurrentUserName
End Function

Chuck Wood

You should change:

UPDATE AuditLog SET AuditLog.AuditUser = GetUserName(), AuditLog.AuditDate = Date()
WHERE (((AuditLog.Log_ID)=[Forms]![Member Main Form]![ACH subform].[Form]![AuditLog subform].[Form]![Log_ID]));

To:

UPDATE AuditLog SET AuditLog.AuditUser = GetCurrentUserName(), AuditLog.AuditDate = Date()
WHERE (((AuditLog.Log_ID)=[Forms]![Member Main Form]![ACH subform].[Form]![AuditLog subform].[Form]![Log_ID]));

Because that is what you named your funciton.
-Chuck

mkwest

ASKER

When I compiled I got an error from the function get computer name so I removed it that Function.

Then, leaving only the GetUserName function, I got an error "Variable not defined" and the code stops at gUsrNm =
Do I need to add dim gUsrNm as string?

mkwest

ASKER

cwood: Thanks. Tried it still get the undefined function, even using GetCurrentUserName()

SOLUTION

Chuck Wood

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mkwest

ASKER

Combined these answers to make it go:

1. Moved the code to a standard module.
2. Removed the code using gUsrNm and the frm.FindInvoices which was obviously left from the original developer's application
3. Referenced GetCurrentUserName() in my SQL

It Works! Many thanks for the prompt and clear help.

Nick67

Once upon a time, Dev Ashish worked out the functions for username and computername
Here is the one for returnusername:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function ReturnUserName() 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
        ReturnUserName = Left$(strUserName, lngLen - 1)
    Else
        ReturnUserName = vbNullString
    End If
End Function

Open in new window

They are simple to use
and came originally from here
http://access.mvps.org/access/

Put the code in a module by itself, not a forms module.
In VBA, in the left pane, right click the modules folder and insert a module
Put the code there.

They get used very simply

ReturnUserName
or
Call ReturnUserName()

To test them, put this code in a button's click event

Msgbox ReturnUserName

Once you know you've got it working, your SQL becomes

UPDATE AuditLog SET AuditLog.AuditUser = ReturnUserName, AuditLog.AuditDate = Date()
WHERE (((AuditLog.Log_ID)=[Forms]![Member Main Form]![ACH subform].[Form]![AuditLog subform].[Form]![Log_ID]));

There is a good discussion with links here:
Work your way all the way to the end
https://www.experts-exchange.com/questions/26881608/User-proofing-an-Access-Application.html

mkwest

ASKER

Thanks, will check it out.

GetUserName &quot;Undefined funtion GetUserName() in expression&quot;

GetUserName "Undefined funtion GetUserName() in expression"