Retrieve in User Login name update Combo

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

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

Select allOpen 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 & "'")

Select allOpen 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

Select allOpen in new window

Thanks,

K

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 Table:

thanks for both of you for your input and time