Solved

MS Access VBA code User ID

Posted on 2009-04-01
3
756 Views
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)))

Else

  fOSUsername = 0

End If

End Function

Open in new window

0
Comment
Question by:lstad
3 Comments
 
LVL 4

Accepted Solution

by:
paisleym earned 500 total points
ID: 24045566
Hi

You need to change

Function fOSUsername() As Long

to
Function fOSUsername() As string

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

to
if fOSUsername = "0"    

Marcelle
0
 
LVL 11

Expert Comment

by:mildurait
ID: 24046203
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

0
 

Author Closing Comment

by:lstad
ID: 31565596
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.  
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

16 Experts available now in Live!

Get 1:1 Help Now