?
Solved

declaring constant variables using if's and elsif's

Posted on 1999-07-20
23
Medium Priority
?
1,102 Views
Last Modified: 2006-11-17
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?
0
Comment
Question by:SeanGraflund
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 7
23 Comments
 
LVL 4

Accepted Solution

by:
tomook earned 120 total points
ID: 2001142
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
 
LVL 1

Author Comment

by:SeanGraflund
ID: 2001143
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
 
LVL 12

Expert Comment

by:Trygve
ID: 2001144
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 4

Expert Comment

by:tomook
ID: 2001145
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
 
LVL 12

Expert Comment

by:Trygve
ID: 2001146
tomook: Why is this more optimized that my solution? Two functions + a global variable...
0
 
LVL 4

Expert Comment

by:tomook
ID: 2001147
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
 
LVL 12

Expert Comment

by:Trygve
ID: 2001148
You are right about that, but is this 12 user setup worth the extra code?
0
 
LVL 4

Expert Comment

by:tomook
ID: 2001149
If you are using it to populate a query, yes. If you will call it once per session, no.
0
 
LVL 4

Expert Comment

by:tomook
ID: 2001150
And besides, with the bloat already in Access, is six lines of code so much to worry about?
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2001151
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
 
LVL 4

Expert Comment

by:tomook
ID: 2001152
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
 
LVL 12

Expert Comment

by:Trygve
ID: 2001153
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
 
LVL 4

Expert Comment

by:tomook
ID: 2001154
But you must have an instance of the class somewhere. Does it not go away when you reset the project?
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2001155
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
 
LVL 4

Expert Comment

by:tomook
ID: 2001156
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
 
LVL 1

Author Comment

by:SeanGraflund
ID: 2001157
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
 
LVL 1

Author Comment

by:SeanGraflund
ID: 2001158
er .. trygve .. :) sorry.  hehe.  typo
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2001159
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
 
LVL 1

Author Comment

by:SeanGraflund
ID: 2001160
so to use no interaction I could use this:?

gUserName = (CurrentUser)

?
0
 
LVL 1

Author Comment

by:SeanGraflund
ID: 2001161
er .. sorry, I mean Me.UserName = gUserName(CurrentUser)
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2001162
Yes
0
 
LVL 1

Author Comment

by:SeanGraflund
ID: 2001163
TEST
0
 
LVL 1

Author Comment

by:SeanGraflund
ID: 2001164
thanks dude!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question