Need help, not sure if this is correct... (1 Viewer)

[ianacole]

I am not sure that this is the right subforum for this topic. I am having some trouble with code. In the following, I am trying to eliminate the lengthy "if-then-else" statement (which does what I need, but needs coding everytime employees change) by comparing the logon name to the names in tblOSLogonName. The function needs to return the full name (2nd column tblOSLogonName).

---------------
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

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If lngX <> 0 Then
strCurrLogon = Left$(strUserName, lngLen - 1)
Else
strCurrLogon = ""
End If

' If strCurrLogon = "icole" Then
' fOSUserName = "Ian Cole"
' ElseIf strCurrLogon = "jmead" Then
' fOSUserName = "John Meade"
' ElseIf strCurrLogon = "dmolloy" Then
' fOSUserName = "Desi Molloy"
' ElseIf strCurrentLogon = "gmatheny" Then
' fOSUserName = "Greg Matheny"
' ElseIf strCurrentLogon = "sjohnstone" Then
' fOSUserName = "Sean Johnstone"
' End If

If strCurrLogon = tblCall!OSLogonName Then
fOSUserName = tblCall!Username
Else
fOSUserName = ""
End If

End Function
-------------------------

Thanks for any help,

Ian

 

[Fornatian]

Ian,

It would be better to set a table up in Access with users login and aliases then use a DLookUp function to retrieve the alias and set it to a variable as follows:

DLookup("[Alias]","[UsersTable]","[NTName] = '" & strCurrLogon & "'"

When new users come on all you need to do is add them to the users table.

HTH

Ian

 

[ianacole]

I've set up the dlookup command, but I'm getting a run-time error 2471: "...strings...not allowed as Public members of object modules. Again the code, revised with the dlookup command, and the if-then-else removed. The table with the OSLogonName and the UserName is tblOSLogonName.
-------------------------------------------
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 strCurrLogon As String
Dim varAliasLookup As Variant

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If lngX <> 0 Then
strCurrLogon = Left$(strUserName, lngLen - 1)
Else
strCurrLogon = ""
End If

varAliasLookup = DLookup("[UserName]", "tblOSLogonName", "[OSLogonName] = " & strCurrLogon)
fOSUserName = "[varAliasLookup]"

End Function
----------------------------------

Thanks for help,

Ian

 

[Fornatian]

Instead of:

varAliasLookup = DLookup("[UserName]", "tblOSLogonName", "[OSLogonName] = " & strCurrLogon)
fOSUserName = "[varAliasLookup]"

TRY

fOSUserName = DLookup("[UserName]", "tblOSLogonName", "[OSLogonName] = '" & strCurrLogon & "'")

When you call this function you need to determine what happens if they aren't logged on suchas:

If fOSUserName = "" Then
Msgbox "You must be logged onto the network to use this database. This DB will now close",0,"LOG ON YOU IDIOT!"
Docmd.Quit
End if

You may want refine the code above!

Ian

End Function
----------------------------------

Thanks for help,


[This message has been edited by Fornatian (edited 01-10-2002).]

[This message has been edited by Fornatian (edited 01-10-2002).]

 

[ianacole]

That got it!! Thanks for the help with the syntax!! And I apologize for the double post.

Just out of curiousity, what is the difference between the single quote and double quote in VBA?

Ian

 

[Fornatian]

I'm not completely sure ot be honest but I do know that if you are using text variables as comparators when building an SQL string or DLookup/DSum etc you always need to enclose the variable in single quotes as I did because when it gets compiled(that's the wrong syntax) it will be right. For instance:

Dim strExample as string
Dim strSQL as string

strExample = "Food"

strSQL = "Select * from MyTable Where MyTable.Dinner = '" & strExample & "';"

The strSQL variable when run would actually look like:

Select * from MyTable Where MyTable.Dinner = 'Food';

Hope that helps a bit.

Ian