• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 110
  • Last Modified:

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.
0
Karen Schaefer
Asked:
Karen Schaefer
  • 10
  • 6
  • 3
3 Solutions
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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 MVPCommented:
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 MVPCommented:
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 MVPCommented:
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 MVPCommented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for both of you for your input and time
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now