Dlookup with string variable

[RECrerar]

Hi, I'm trying to use the users network login name as a rudimentary security method so that people can only edit record in a database for which their name appears in one of the fields, see this link for the discussion on this http://www.access-programmers.co.uk/forums/showthread.php?t=154562

I have this working but now want to add an extra layer that if the person's position is listed as "Administrator" in the [Position] field of the [Users] table then they can edit any of the records. I am trying to do this using Dlookup with the code below.

Login.Enabled = True
Login.SetFocus
stAdminLogon = ap_GetUserName()
vAdminLogon = DLookup("[Position]", "[Users]", "[Login]='" & stAdminLogon & "'")
If Me.Login <> stAdminLogon And vAdminLogon <> "Administrator" Then
    Me.AllowEdits = False
Else
    Me.AllowEdits = True
End If
Me.Text6.Enabled = False
Position.SetFocus
Login.Enabled = False

The Dlookup command however isn't working and comes up with the error:

Runtime Error '3705'
Syntax error in string in querry expression '[Login]='CRERAR_R'

The code behing ap_GetuserName is

Declare Function wu_GetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) _
    As Long
 
Function ap_GetUserName() As Variant
    'Returns the network login name
    Dim strUserName As String
    Dim lngLength As Long
    Dim lngResult As Long
    '-- Set up the buffer
    strUserName = String$(255, 0)
    lngLength = 255
    '-- Make the call
    lngResult = wu_GetUserName(strUserName, lngLength)
    '-- Assign the value
    If lngResult <> 0 Then
        ap_GetUserName = RTrim(strUserName)
    Else
        ap_GetUserName = ""
    End If
End Function

I am assuming it soemthing to do with the possition of the quote marks in teh Dlookup but have tried every combination I can think of. Can anyone help?

Also. If I were to use the standard access secruity to give administrtion rights to certain people would this overwrite the AllowEdits command? My thinking is that the AllowEdits = falsue would override anything else so that is why I have not tried this yet.

Finally. Is there anyway to extract the value from a hidden or disabled field in a form as although the code works by enabling and then disabling the login field I would really prefer to just have it hidden as it would be preferable for all users not to be able to see everyone elses login name for obvious resons.

Thanks

 

[RECrerar]

I've figured it out, not too sure why this works but replaced the line

ap_GetUserName = RTrim(strUserName)

with

ap_GetUserName = Left$(strUserName, lngLength - 1)

and reduced the initially defined string length and all works fine now. I assume that it is to do withe the different functionalities of Trim and Left, the Trim code didn't seem to actually affect the length of the string whereas the Left does. If anyone can explain this difference that would be good else I shall just accept it and move on.

 

[DJkarl]

I've figured it out, not too sure why this works but replaced the line

ap_GetUserName = RTrim(strUserName)

with

ap_GetUserName = Left$(strUserName, lngLength - 1)

and reduced the initially defined string length and all works fine now. I assume that it is to do withe the different functionalities of Trim and Left, the Trim code didn't seem to actually affect the length of the string whereas the Left does. If anyone can explain this difference that would be good else I shall just accept it and move on.

Trim only removes excess spaces from the beginning and end of a string, left takes the left most characters of a specified length. In your example I suspect the extra character was not a space but another ASCII character...likely Chr(0).

 

[namliam]

Use the native access function instead:
Environ("Username")

Your solution works because there is a "null value string" IIRC at the end of the username...
This last character as mentioned by DJKarl is unreadable... But definatly NO space as they are removed by Trim.

 

[RECrerar]

In your example I suspect the extra character was not a space but another ASCII character...likely Chr(0).

You are exactly right, the rest of the string was made up of char(0) so that explains it.

Use the native access function instead:
Environ("Username")

That is fantastic, that is so much simpler than all the code I had for it before. Thanks, I think I will switch to that method.