declaring constant variables using if's and elsif's

I have a workgroup that stores userid's and pwd's, these are in the format of the user's windows nt logon id.

in a startup function, I'd like to parse the ID's into the person's name.

IE:

Public Function Startup()

If CurrentUser = "sgrafl01"
    const username as String = "Sean Graflund"
Elseif CurrentUser = "dmarti01"
    const username as String = "Donald Martin"
ElseIf .... and so on

End if

End Function

I have about 12 logon ID's I'd do this with.  It works fine with standard global strings, but I'd like to turn it into a constant.  For some reason, after using the DB for awhile the global string turns to a zero lengh string.  If I use a const string, it will always have it.

When I try to declare this as a constant, VBA gives a declaration declared multiple times error.  Is there a way I can assign this as a const without getting that error or will I have to change all the User ID's to the correct person's name in the workgroup manager instead of using the same ID as is used for the Windows NT logon?
LVL 1
SeanGraflundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tomookCommented:
Declare a public variable:

Public g_username As String

Then set the value in your function:
Public Function Startup()

If CurrentUser = "sgrafl01"
    g_username = "Sean Graflund"
Elseif CurrentUser = "dmarti01"
    g_username = "Donald Martin"
ElseIf .... and so on

End if
End Function

P.S. You may find it easier and more maintainable to put these values in a table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SeanGraflundAuthor Commented:
but what would the difference between public and global be?  if the code halts for any reason and the project is reset, would the public variable be set to a zero lengh string?

I do this now with a global variable.
0
TrygveCommented:
Instead of using the global string I would let the function return the name. This way, crashes etc. will not matter. The function can be used directly from queries, in controlsource on forms and reports etc. etc. Much better

Something like this will do;

Public Function gUserName(pLoginID)

  pLoginID = nz(pLoginID,vbNullString) ' To prevent problems form NULLs
  Select Case pLoginID
    Case "sgrafl01"
      gUserName= "Sean Graflund"
    Case "dmarti01"
      gUserName= "Donald Martin"
    Case Else
      gUserName = "Unknown"
  End Select

End Function
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

tomookCommented:
I see the problem you are trying to solve. I have worked around it in the past with the following technique (similar to Trygve's note but a little more optimized).

A) Declare a global
Public g_username As String

B) Write a function
Public Sub gSetName()
If CurrentUser = "sgrafl01"
    g_username = "Sean Graflund"
Elseif CurrentUser = "dmarti01"
    g_username = "Donald Martin"
ElseIf .... and so on
End if
End Sub

C) Write a function to return the value
Public Function MyUserName() As String
  If g_username = "" Then
    gSetName
  End If
  MyUserName = g_username
End Function

This has the added benefit that you can use the value in a query since you can call functions but not reference global variables in a query.
0
TrygveCommented:
tomook: Why is this more optimized that my solution? Two functions + a global variable...
0
tomookCommented:
Run time speed is optimized. You process only one "If" statement when the variable is initialized, rather than traversing the Case structure (each Case counts as an If) each time you want the value. A classic case of trading code for speed.
0
TrygveCommented:
You are right about that, but is this 12 user setup worth the extra code?
0
tomookCommented:
If you are using it to populate a query, yes. If you will call it once per session, no.
0
tomookCommented:
And besides, with the bloat already in Access, is six lines of code so much to worry about?
0
TrygveCommented:
If this funcion is supposed to look up the current users name then it does not have to be called more that once, even from a query that produces n records. The function will only be executed once for each record if you provide it with a field from one of the tables as a parameter.

But I agree: Your solution is a bit more smooth-running and will perform faster. I in fact use a class collection to store values I pick up from tables or other lookups. This makes it even more elegant than using a global variable for each value.
0
tomookCommented:
You still run into the same problem with a class as with variables: if you reset the project all the information is gone. I believe that is what SeanGraflund was trying to address.
0
TrygveCommented:
That is why my code checks the class to see if the stored value I want can be retrieved. If not it looks the value up from wherever it is located, puts it into the class and returns it to the caller.
0
tomookCommented:
But you must have an instance of the class somewhere. Does it not go away when you reset the project?
0
TrygveCommented:
Yes, but the code that checks for the "cached" value will look it up and set it if it is not already there.

Attempting to refer to the "property" of the class will return an error if it has not yet been set, this is trapped and lead my code to the part where it sets "property".
0
tomookCommented:
I now see how you handle it. I will have to ponder that style a while. Right now I wonder if we have solved SeanGraflund's problem. Any feedback, SeanGraflund?
0
SeanGraflundAuthor Commented:
I like Tygrave's solution, but what is the actual variable name that's stored? .. is it pLoginID or gUserName?  In my DB, in many forms, when a record is added, I use the FormLoad event to assign variables like company_id Foreign Keys and so on.  I do this also w/ the UserName.  Like I call Me.UserName = UserName .. so the User doesn't have to select the name from a listbox or something ..

How do I call the username from Tygrave's function?  That I don't understand..

Thanks,

0
SeanGraflundAuthor Commented:
er .. trygve .. :) sorry.  hehe.  typo
0
TrygveCommented:
From a control or in a query use gUserName("The login of the user you want the name for"). The username is not stored in a variable but returned each time you ask for it.
0
SeanGraflundAuthor Commented:
so to use no interaction I could use this:?

gUserName = (CurrentUser)

?
0
SeanGraflundAuthor Commented:
er .. sorry, I mean Me.UserName = gUserName(CurrentUser)
0
TrygveCommented:
Yes
0
SeanGraflundAuthor Commented:
TEST
0
SeanGraflundAuthor Commented:
thanks dude!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.