MS Access VBA code User ID

Posted on 2009-04-01
Last Modified: 2013-11-27
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)))


  fOSUsername = 0

End If

End Function

Open in new window

Question by:lstad

Accepted Solution

paisleym earned 500 total points
Comment Utility

You need to change

Function fOSUsername() As Long

Function fOSUsername() As string

You may need to also change the return code test from
if fOSUsername = 0    

if fOSUsername = "0"    

LVL 11

Expert Comment

Comment Utility
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



    fOSUserName = "Unknown"

End Function

Open in new window


Author Closing Comment

Comment Utility
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.  

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

744 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

18 Experts available now in Live!

Get 1:1 Help Now