fOSUserName

[johnlute]

Access 2003.

I found a 2008 post by Arvin Meyer and am having trouble with it:

I use the api code to identify the Windows authenticated username:
http://www.mvps.org/access/api/api0008.htm

and simply add the If statement to the form's code:
If fOSUserName() = "Arvin" Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "You are not allowed to use this form", vbOKOnly
DoCmd.CancelEvent
End If

I've created the fOSUserName module and used a similar If statement in
the command button that opens the form that I want:

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmEditSpecifications"

If fOSUserName() = "myusername" Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "Sorry! You are not allowed to use this form.",
vbCritical, vbOKOnly
DoCmd.CancelEvent
End If

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub

This results in a compile error: expected variable or procedure, not a
module. The debugger points to > fOSUserName

If I change this from fOSUserName() to "fOSUserName" then it compiles
but when I click the button I get the not permitted message.

Two questions:
1. Was my correction correct?
2. If so, is it possible that my username isn't what I think it is?

Thanks for your help!

 

[Mike B]

Make your Function Public like:

Public Function fOSUserName() 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
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

 

[johnlute]

Hi, Mike.

Thanks for the suggestion. Unfortunately, that resulted in the same
compile error noted above:
error: expected variable or procedure, not a
module.

Any other ideas? This is absolutely maddening!

 

[Daryl S]

Johnlute -

You should not have procedure names the same as module names. Change your
module name to "Utilites" or something.

 

[johnlute]

Thanks, Daryl. That is absolutely correct - I can't believe I didn't
see that - thanks!

 

[johnlute]

Now I've got another hiccup.

I've decided to do a lookup for user names. I created a table and
adjusted the If statement to:

If fOSUserName = DLookup("txtUserName", "tblUserNames") Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "Sorry! You are not allowed to use this form.",
vbCritical, vbOKOnly
DoCmd.CancelEvent
End If

I get the "not allowed" message but if I change it to <> DLookup or >=
DLookup then it opens the form - so I guess I have it written
correctly...? Why is "=" not working?

Or have I missed the boat completely?

Thanks for your help!

 

[johnlute]

More experimenting - I removed all user names except mine from the
table and then the form opened. I then added one other name besides
mine and the error message fired.

So this means that the lookup is only able to find my name if it's the
ONLY name in the table.

This just isn't what I'd expect from DLookup - or am I was off
base...?

 

[Dirk Goldgar]

More experimenting - I removed all user names except mine from the table
and then the form opened. I then added one other name besides mine and the
error message fired.

So this means that the lookup is only able to find my name if it's the
ONLY name in the table.

This just isn't what I'd expect from DLookup - or am I was offbase...?

You need to tell it which name to look up; otherwise, it will just return
the first record it happens to pull from the table. Try this:

'------ start of revised code ------
If IsNull( _
DLookup( _
"txtUserName", "tblUserNames", _
"txtUserName = " & Chr(34) & fOSUserName() & Chr(34) _
) _
) _
Then
MsgBox _
"Sorry! You are not allowed to use this form.", _
vbCritical, vbOKOnly
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
'------ end of revised code ------

I removed the DoCmd.CancelEvent, because you can't cancel a command button's
Click event. You just won't do what it says.

Also, I'm assuming that you don't need the user name for anything else, so
I'm not saving it in a variable. All the code above does is check to see if
it's in the table. If it isn't there, DLookup will return Null.

 

[johnlute]

Hi, Dirk!

You need to tell it which name to look up; otherwise, it will just return
the first record it happens to pull from the table.

So I was WAY off base, indeed...

 Try this:

'------ start of revised code ------
    If IsNull( _
        DLookup( _
            "txtUserName", "tblUserNames", _
            "txtUserName = " & Chr(34) & fOSUserName() & Chr(34) _
            ) _
        ) _
    Then
            MsgBox _
                "Sorry! You are not allowed to use this form.", _
                vbCritical, vbOKOnly
    Else
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
'------ end of revised code ------

Well, that works smooth as silk!

I removed the DoCmd.CancelEvent, because you can't cancel a command button's
Click event.  You just won't do what it says.

That makes sense - not sure what I was thinking there, either!

Also, I'm assuming that you don't need the user name for anything else, so
I'm not saving it in a variable.  All the code above does is check to see if
it's in the table.  If it isn't there, DLookup will return Null.

That is the correct assumption.

Thanks, Dirk! You saved me from burning a few more brain cells - and
I'm running very low on those!!!

 

[Mike B]

Also shouldn't he do a UCase on both sides of the operand to be certain the
table entry isn't cased differently than the return from the API?

 

[Dirk Goldgar]

Also shouldn't he do a UCase on both sides of the operand to be certain
the table entry isn't cased differently than the return from the API?

By default, the comparison will not be case-sensitive, so "john" = "John" =
"jOhN". If he *wants* it to be case-sensitive, then he has to do something
different.

 

[John W. Vinson]

Also shouldn't he do a UCase on both sides of the operand to be certain the
table entry isn't cased differently than the return from the API?

Not necessary if the table is in Access - searches are not case sensitive.

 

[johnlute]

Excellent points! The default is fine but I'll have to save this
thread should I need to explore case sensitivity in the future.

Thanks!

 

[Daryl S]

No need for UCase if you use the default settings that use a case-insensitive
match.

 

[Mike B]

By default, the comparison will not be case-sensitive, so "john" = "John"
= "jOhN". If he *wants* it to be case-sensitive, then he has to do
something different.

is it different in DLookUp? The following says differently.

Sub TestCaseSens()

If "john" = "JOHN" Then
MsgBox "not case sensitive"
Else
MsgBox "case sensitive"
End If


End Sub

 

[Mike B]

Not necessary if the table is in Access - searches are not case sensitive.
--

Ok. Thanks John. I sent my response to Dirk before I read yours. Sorry.

 

[Dirk Goldgar]

is it different in DLookUp? The following says differently.

Sub TestCaseSens()

If "john" = "JOHN" Then
MsgBox "not case sensitive"
Else
MsgBox "case sensitive"
End If


End Sub

When I put that Sub into a standard module and run it, I get the message
"not case sensitive". What do you get? Are you running it in a module
where you've replaced the default "Option Compare Database" with "Option
Compare Binary"?

 

[Mike B]

When I put that Sub into a standard module and run it, I get the message
"not case sensitive". What do you get? Are you running it in a module
where you've replaced the default "Option Compare Database" with "Option
Compare Binary"?

In a Standard Module with "Option Explicit".

I see if "Option Compare Database" is added, the inverse is the result. I
did not know this.

Though my activities with Access are largely hobby level, I have done a few
relatively extensive applications and never uncovered this on my own.

I always UCase or LCase the compare values to be certain as I am used to
doing this in Pascal. Also, when using Server Side scripting in VBS, I
would be forced to use xCase as well, so I guess there's no real harm in
being habituated to doing the extra step, but it is good to know there is a
difference in the VBA environment. Thanks, Mike