asked on

MS Access VBA code User ID

I have minimal VBA experience and copied this code several years ago. The code recognizes the user's login I.D. . The function "fosusername()" can be placed in query to filter on data for that user or restrict access to other data. The problem I am having with the code is that the User I.D.'s are no longer all numeric. The ID's now contain alpha characters. For example, a user would login with the numeric ID# 0020411, but now would use 00B0411. The code trims the ID# to 5 characters as those are the primary search digits (20411 or B0411). I need this code to read both both numeric and alpha-numeric ID's. I'm using this in a 2003 MS Access database.

Option Compare Database
 
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Function fOSUsername() As Long
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 
    strUserName = Left$(strUserName, lngLen - 1)
    fOSUsername = Val(Trim(Right(strUserName, 5)))
Else
  fOSUsername = 0
End If
End Function

Open in new window

ASKER CERTIFIED SOLUTION

paisleym

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mildurait

You could try this code.

Option Compare Database
Option Explicit
 
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function fOSUserName()
 
    On Error GoTo Exit_label
 
     Dim lpBuff As String * 25
     Dim ret As Long, Username As String
     ret = GetUserName(lpBuff, 25)
     Username = Trim(lpBuff)
     
     Dim i As Long
     i = InStr(Username, Chr(0))
     Username = Left(Username, i - 1)
     fOSUserName = Trim(Username)       
    Exit Function
    
Exit_label:
    fOSUserName = "Unknown"
 
End Function

Open in new window

lstad

ASKER

Thanks for helping get on the right track with this code. I broke the code down to smaller modules and found the the "val" script in the fosusername trim statement was creating the bulk of the problem, and I deleted the "If" statement.