Solved

MS Access VBA code User ID

Posted on 2009-04-01
3
769 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dsum Function for List Box Data 7 45
ms access 2010 vba, copy table from db1 to db2 from db3 27 46
Add records to a form to a table 11 39
Any Way to Print an Import Spec? 3 29
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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