Link to home
Create AccountLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

asked on

Retrieve in User Login name update Combo

Having a issue with vba to auto populate a field(combo = id, Name, Email, LoginName.

Private Sub Approved_AfterUpdate()
If Me.Approved = True Then
    Me.Approved_by = DLookup("ID", "Users", "Login =" & "'" & GetUserName & "'")
    Debug.Print Me.Approved_by
End If
End Sub

Open in new window


Using the standatd GetUserName functiion.

The current code is erroring on the following "Cannot perform this operation"..

Any ideas.
Avatar of Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
Flag of United States of America image

The built in Access 2003 function is CurrentUser not GetUserName.  This also requires using an .mdb and ULS.

Are you trying to get the  Current Windows user name?  If yes, try this: http://access.mvps.org/access/api/api0008.htm

Otherwise it would help to see the code for use are using for your "standard" GetUserName function
Avatar of Karen Schaefer

ASKER

I am not using 2003 but 2010 - here is the get User function

Option Compare Database
Option Explicit

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

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

Open in new window

I don't believe the issue is with the function but possibly with the Dlookup function syntax.  I am currently using the get user function elsewhere without issue.

K
What happens when you run the folowing in the immediate window (ctrl-g) of the VBA editor?

? DLookup("[ID]", "[Users]", "[Login] =" & "'" & GetUserName & "'")

Open in new window

You may be returning a null value, so wrap it in the Nz function:

Me.Approved_by = DLookup("ID", "Users", "Login ='" & Nz(GetUserName, "") & "'")

Also check spelling of Tables and Columns.
I have tried both suggestions and I am still getting the same error.

When I use the 1st suggestion it returns the value of 1 which is correct.

I also tried placing the NZ @ beginning of Dlookup statement - no difference.

I forgot to mention that the value of the "Approved by" is a combo and multiple selection option is on.

When I step thru the code the GetUserName works correctly, however setting the value to the correct field fails.

Private Sub Approved_AfterUpdate()
If Me.Approved = True Then
    'Me.Approved_by = DLookup("ID", "Users", "Login =" & "'" & Nz(GetUserName) & "'")
    Me.Approved_by = Nz(DLookup("ID", "Users", "Login ='" & Nz(GetUserName, "") & "'"))
    Debug.Print Me.Approved_by
End If
End Sub

Open in new window


Thanks,

K
SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
What are you expecting to be return from the Dlookup?

Your DLookup is programmed to return the ID field from the table Users were the field Logon is equal to  the Windows User name.  Is that what you want?
the Id field is the value stored but not the value displayed - would that make any difference?
What are you expecting to be return from the Dlookup?
here is the query behind the combo:

SELECT Users.ID, Users.Login, Users.FullName
FROM Users
ORDER BY Users.[FullName];

column count = 3
widths = 0";1";1"

On Select of the Approved checkbox I want to set the value = the current user  based on results from GetUserName function.  The Results format can contain multiple character types -  "a-username" or just Username.

Hope this helps.

k
What's the Bound Column of the Combo?

What is the ControlSource of the Combo?
If the ID is appearing in the Me.Approved_by combo box then it is probably not bound to column 1.
column 1 is the bound column.

Control Source = Approved by
Here is a copy of property sheet for combo:
User generated image
User Table:
User generated image
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
thanks for both of you for your input and time