DLookup

[Aubrey]

Dear all,

In my project I have this line of code-

strRole = Nz(DLookup("[Role]", "tblStaff", "[UserID] ='" & UserName & "'"), "")

It works fine until someone has an ' in their name...can anyone tell me what to do about this?

Thanks

Aubrey

 

[KenHigg]

Maybe:

strRole = Nz(DLookup("[Role]", "tblStaff", "[UserID] =""" & UserName & """"), "")

??

 

[Aubrey]

nope...that just takes username as username.

username has been found using a module to find the login name of the currently logged in user.

any other ideas?!

 

[KenHigg]

Is username a string variable that you have defined in the module?

 

[Aubrey]

yeah

the module is...

Option Compare Database
Option Explicit
Private Const UNKNOWN = "(Value Unknown Because System Call Failed)"
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public UserID As String
Public Function GetCurrentUserName() As String 'find the user name of the currently logged in user
Dim l As Long
Dim sUser As String
sUser = Space$(255)
l = GetUserName(sUser, 255)
If l <> 0 Then
   GetCurrentUserName = Left(sUser, InStr(sUser, Chr(0)) - 1)
Else
   Err.Raise Err.LastDllError, , "A system call returned an error code of " & Err.LastDllError
End If
End Function

so to get username i just run...

username = getcurrentusername()

 

[KenHigg]

strRole = DLookup("[Role]", "tblStaff", "[UserID] = " & chr(32) & UserName & chr(32))

??

 

[Aubrey]

that gives me an error message when i open the form =S

error message- "you cancelled the previous operation"

 

[KenHigg]

How far along are you in building all of this?

 

[Aubrey]

i went into beta testing today and this came up...so basically i'm finished apart from this...whys that?

 

[KenHigg]

Well there are a couple of things I'd change but not if you have all of it built. Can you simply prevent the special characters by using an input mask when you enter a user name?

If not maybe somebody else can help...

 

[Aubrey]

that wouldn't work though as the username is taken from the login details so i can't change the name on the system.

how drastic are your changes?

 

[KenHigg]

Well... It looks like you have a one user/one role defined. This will not allow a user to have the rights of multiple roles. This may work fine for your db but it's just something to think about next time. Second, and you may be doing this but I would set up the user name in a global variable so that you only go after it once.

So you get the system name and duplicate it in your own table? Did you have to write all of that string$ stuff? I've never seen all of that but it's probably a system thing... If nobody chimes in in a few minutes I'll try to replicate what you're doing and help you come up with a solution...

 

[Aubrey]

i think i have it sorted now...i've used a replace function to remove the ' from the name and it now works!

and no i didn't write the module...i found it on the internet...i've only been programming for less than a year so i'm not that good yet!

 

[KenHigg]

Cool - Glad you have it working. Thanks for posting the solution.

 

[Aubrey]

No problem...cheers for helping