Run-time error 2001 on DLookup

  • Thread starter Thread starter Emma
  • Start date Start date
E

Emma

My "main switchboard" screen calls a function to find out
the user's network name. (I got the code from a MS
support site.) It works fine, but I need to verify that
the user is registered in tblEmployees. The reason is
because only the person who appears in a particular
record will be able to edit the record in the form.

I've added a DLookup to tblEmployees, but keep getting
Run-Time Error 2001 which says "You canceled the previous
operation." I click on Help, but get a blank screen and
can't find an explanation of what's wrong. I have tried
putting the DLookUp in various forms, always with the
same results. A watch on fOSUserName appears in the forms.

Here is the code:
-----------------------------
Option Compare Database

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

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
Dim mFirstName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
mFirstName = DLookup
("[FirstName]", "tblEmployees", "[UserNTID] =
fOSUserName")
Else
fOSUserName = vbNullString
End If
End Function
 
Try
mFirstName = DLookup ("[FirstName]", "tblEmployees",
"[UserNTID] = '" & fOSUserName & "'")

Hope This Helps
Gerald Stanley MCSD
 
This error usually means that something in the DLookup function's syntax is
not correct. Things to look for:
(1) a misspelled field name
(2) using a field name that isn't in the table/query
(3) erroneous "WHERE" criterion statement (including
improper syntax such as not delimiting a text
string with ' characters if the field is formatted
as a text field)

In your case, I believe that Gerald's answer elsethread is the solution to
this error.

Also note that you do not need to use the [ ] in the first argument as a
delimiter for the field name.
 
Hi,
You do realize that your trying to call your function (fOSUserName) from within itself?
This is not a good idea.
If you want to use the username in your DLookup, asign the return from the API call to a
variable first and then use that in your DLookup call (following Gerald's advice on how to
'quote'
the criteria).
 
Thank you Gerald, Dan and Ken

Gerald's revised code works. It is often difficult for
newbies, like me, to see the error of our ways until
someone shows us the light! It's especially nice to be
able to see code in the context in which we're working.

I was really confused about Dan's comment that I was
calling the function from within itself. Since the
original code came from Microsoft I thought it was okay.
It took a while to figure out. For other's to see, I've
shown the revised code below. Not sure if it's completely
right, but it seems to be working...

Option Compare Database

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

Function fOSUserName(strUser) As String

' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
'Dim strUser As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
'fOSUserName = Left$(strUserName, lngLen - 1)
strUser = Left$(strUserName, lngLen - 1)
'Get their EmployeeID for use in other forms to
validate their rights
mCurUser = DLookup
("[EmployeeID]", "tblEmployees", "[UserNTID] = '" &
strUser & "'")

Else
fOSUserName = vbNullString
strUser = vbNullString
End If

End Function
 
Back
Top