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.
Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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
0
Karen SchaeferBI ANALYSTAuthor Commented:
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

0
Karen SchaeferBI ANALYSTAuthor Commented:
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
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
Karen SchaeferBI ANALYSTAuthor Commented:
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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I forgot to mention that the value of the "Approved by" is a combo and multiple selection option is on.
There is no "multiple selection" option in a combo, unless you're using one of the multi-valued field abominations - and if you are, then you should seriously rethink your design.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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?
0
Karen SchaeferBI ANALYSTAuthor Commented:
the Id field is the value stored but not the value displayed - would that make any difference?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
What are you expecting to be return from the Dlookup?
0
Karen SchaeferBI ANALYSTAuthor Commented:
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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What's the Bound Column of the Combo?

What is the ControlSource of the Combo?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
If the ID is appearing in the Me.Approved_by combo box then it is probably not bound to column 1.
0
Karen SchaeferBI ANALYSTAuthor Commented:
column 1 is the bound column.

Control Source = Approved by
0
Karen SchaeferBI ANALYSTAuthor Commented:
Here is a copy of property sheet for combo:
Property Sheet
0
Karen SchaeferBI ANALYSTAuthor Commented:
User Table:
Screenshot.png
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Are you able to manually select a value in the combo box and it work?

I would first change the name of the combo box to cboApprovedBy. No spaces in the name and not the same name as the any bfield name in the record source of the form.

The the code would be:

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

Open in new window

0
Karen SchaeferBI ANALYSTAuthor Commented:
Yes the combo allows the user to actually select 1 or more users.  I decided to abandon this approach - I decided to have the user select their name from combo and then update the checkbox and date - this has no issues.

Thanks for great info and your time.

K
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for both of you for your input and time
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.