By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 733 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,241 IT Pros & Developers. It's quick & easy.

Storing sessional User information using VBA

P: 6
Situation:
I have a database that has users with different levels of access to the information they are allowed to view. I have addressed this issue by creating a login form that opens on startup. Behind this form is the tblUserDetails, which has login information such as: userID, password and access level (which has three levels: basic/full/admin).

Problem:
Once the user has logged in I would like to be able to retrieve their userID and access level (AccLvl) for the entire time they are using the database (ie that session). That is, controls of forms are made visible/hidden and enabled/disabled depending on the users access. Currently I am handling this by passing the userID and AccLvl values from one form to the next. In other words, I have two hidden controls on every form (txtuserID and txtAccLvl) and I populate these controls every time I open a new form. I feel this system could be improved.

Question:
Is there a way to store the userID and AccLvl information as a VBA variable? I have not used VBA to store global-sessional information like this before and I am not sure how it would work in my current multi-user networked environment (see below). Any advice would be greatly appreciated.

System Environment:
- Iím using Access 2000.
- The database is located on a network.
- It is expected that there could be up to 5 users at any one time.
- The database is split with both the front end and back end on the network. The main users of the database have copies of the front end set up on their local desktops that link to the networked back ends. However, it is possible (but not common) that more than one user at a time might access the front end which is located on the network.
Nov 22 '06 #1
Share this Question
Share on Google+
10 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
To create Global variables for userID and AccLvl.

Remove the local variable definitions and instead in a Module (standard practice is to create a separate module just for this) create global variables as follows:

Global userID As String
Global AccLvl As Integer ' I assume

These variables are now available thoughout the database and will hold value passed to them unless value is changed.

Mary


Situation:
I have a database that has users with different levels of access to the information they are allowed to view. I have addressed this issue by creating a login form that opens on startup. Behind this form is the tblUserDetails, which has login information such as: userID, password and access level (which has three levels: basic/full/admin).

Problem:
Once the user has logged in I would like to be able to retrieve their userID and access level (AccLvl) for the entire time they are using the database (ie that session). That is, controls of forms are made visible/hidden and enabled/disabled depending on the users access. Currently I am handling this by passing the userID and AccLvl values from one form to the next. In other words, I have two hidden controls on every form (txtuserID and txtAccLvl) and I populate these controls every time I open a new form. I feel this system could be improved.

Question:
Is there a way to store the userID and AccLvl information as a VBA variable? I have not used VBA to store global-sessional information like this before and I am not sure how it would work in my current multi-user networked environment (see below). Any advice would be greatly appreciated.

System Environment:
- Iím using Access 2000.
- The database is located on a network.
- It is expected that there could be up to 5 users at any one time.
- The database is split with both the front end and back end on the network. The main users of the database have copies of the front end set up on their local desktops that link to the networked back ends. However, it is possible (but not common) that more than one user at a time might access the front end which is located on the network.
Nov 23 '06 #2

P: 6
How do Global variables behave in Access when there is more than one user logged into the system? For example, if a restricted user logs in to the front-end and then an administrator logs into the same front-end file... are the Global variables unique to each user? (Ideally people will have different front ends but I cannot always ensure this will be the case).

thanks again


To create Global variables for userID and AccLvl.

Remove the local variable definitions and instead in a Module (standard practice is to create a separate module just for this) create global variables as follows:

Global userID As String
Global AccLvl As Integer ' I assume

These variables are now available thoughout the database and will hold value passed to them unless value is changed.

Mary
Nov 23 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
How do Global variables behave in Access when there is more than one user logged into the system? For example, if a restricted user logs in to the front-end and then an administrator logs into the same front-end file... are the Global variables unique to each user? (Ideally people will have different front ends but I cannot always ensure this will be the case).

thanks again
Every time a front end is open Access creates a new instance of it. So it shouldn't be a problem.
Nov 23 '06 #4

P: 6
many thanks!!

Every time a front end is open Access creates a new instance of it. So it shouldn't be a problem.
Nov 23 '06 #5

NeoPa
Expert Mod 15k+
P: 31,494
Follow on question.
Why are such variables generally stored in a separate, stand-alone, module?
Nov 23 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Follow on question.
Why are such variables generally stored in a separate, stand-alone, module?
Simple answer.

A form module is local to the form and any variable declared publicly in the form module will only be available while that form is open. Of course any variable declared within a procedure or function is only locally declared and therefore only available to that procedure or functions.

There is no facility in VBA to declare a public variable within a procedure or function or to declare a global variable within a form module.

Also don't forget the class module where the variables declared are only available within the instance of the declaration of that class. (just thought I'd complicate the issue ;) )

Whereas a separate, stand-alone module is 'always open' or rather it is available to the whole application.

Mary
Nov 23 '06 #7

NeoPa
Expert Mod 15k+
P: 31,494
Ah, The reason I was asking is that I have a stand-alone module which I use for globally available variables as well as general purpose code.
I thought you were indicating that I should separate these into two modules.

I know my central module is perhaps overly large, but there's no reason to split between Code and Global Variables specifically?
Nov 23 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Ah, The reason I was asking is that I have a stand-alone module which I use for globally available variables as well as general purpose code.
I thought you were indicating that I should separate these into two modules.

I know my central module is perhaps overly large, but there's no reason to split between Code and Global Variables specifically?
No, its just standard practice to split modules out.

e.g. GlobalVariables, SystemCode, MailingFunctions, etc.

Not very neat Adrian for someone who keeps giving out to me about code delimiters. :)

Mary
Nov 23 '06 #9

NeoPa
Expert Mod 15k+
P: 31,494
I'm sorry Mary.
I'm mainly self-taught, so I don't often get to hear what standard practice is. That's why I'm asking now ;).
I generally think there are a lot of good reasons to use standard practice, even if there are no good supporting reasons. If for no better reason than clarity of understanding and communication.
Thanks for your answer.
Nov 23 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm sorry Mary.
I'm mainly self-taught, so I don't often get to hear what standard practice is. That's why I'm asking now ;).
I generally think there are a lot of good reasons to use standard parctice, even if there are no good supporting reasons. If for no better reason than clarity of understanding and communication.
Thanks for your answer.
You're welcome.

The thing about standard practice and naming conventions is the transparancy of applications regardless of the designer(s).

Having said that I don't always follow them myself. Particularly the naming conventions.

Mary
Nov 23 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.