Hi guys n gals,

I have a tricky thing I would like to do with my database and need some expert guidance (I am still learning this stuff so bear with me!   )

Owing to the way our network is setup at work I can't use the group/user permissions for MSAccess (2003), so have created my own form of database security. I have set up a log in table and a form that takes the user input for User ID, and password. My VB code runs off and checks the Me! with whats in the table. If correct the user gets to access the rest of the db (its all guided with forms - no user has access to tables and queries, just forms and reports), if not correct bye bye try again.

The variable that holds the userid is called cboUserID. What I want to do with that is set this as the value for the entire duration of the person's access (but without saving it to a table) and use that value for each of the queries that runs on the available forms. At the moment the user logs in, then re-enters their userid pretty much on each form (as the forms display specific data about their various team members).


1. I don't know how to set the variable as something more static.
2. I don't know how to reference that variable in the query or the SQL syntax.

Can anyone shed some light for me.

P.S. not looking for code (but wont say no if its offered!) just a clue on how to go about it! Ta much for any help.

Hi,
There are several ways in which you can store Variable details in memory. You can create a Global Variable in the Declarations Section of the main code module, so that it will then be visible throughout the project, or if you want to be "neater" from a programming point of view then create a Class to store all your Global data.
The following holds for Access 2002 (Office XP) so I presume it will be much the same for 2003 version.
Select "Insert" from main menu, and click on "Class Module".
You need to create local variables in the declaration section, then create the Properties of your Class. So for your Users it would be good to create Properties for "UserName", "UserPassLevel", and "UserID". Then when the User logs in you set the properties, which you can then reference from code at any time.

Option Compare Database Option Explicit Private mvarUserName As String Private mvarUserID As Long Private mvarUserPassLevel As Integer Public Property Let UserID(ByVal vData As Long)     mvarUserID = vData End Property Public Property Get UserID() As Long     UserID = mvarUserID End Property Public Property Let UserName(ByVal vData As String)     mvarUserName = vData End Property Public Property Get UserName() As String     UserName = mvarUserName End Property Public Property Let UserPassLevel(ByVal vData As Integer)     mvarUserPassLevel = vData End Property Public Property Get UserPassLevel() As Integer     UserPassLevel = mvarUserPassLevel End Property

Save your Class Module as "clsGlobals"
You need to create an instance of the Class when your application starts, so in the Declarations section of the main module of your app declare "Public gvals As clsGlobals" - Then, in the first Procedure that you run when your app opens initialise the Class with the line "Set gvals = New clsGlobals" - Now you can reference any of the properties that you create in the Class from anywhere in your app.
So, when you have checked the credentials of your User you can set the Properties from within that procedure, it would look something like:-

'after the code that returns user details in Recordset gvals.UserID = Recordset("user_id") gvals.UserPassLevel = Recordset("user_security") gvals.UserName = Recordset("user_name")

So, when you want to run a Query with your Users ID in it then you can access the information directly from your Class Property :-

Dim strSQL As String strSQL = "SELECT TableName.* FROM TableName " _ & "WHERE (((TableName.user_id)=" & gvals.UserID & "));"

Hope that helps.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

GeoffS, thank you, thank you a million thank you's.

I really do appreciate your reply, and the fact that you have explained it in a way that I can really absorb has set the foundation for the rest of my VB understanding :-)

To say I was clueless with this before was an understatement, now with your reply I am confident to do this again and again no matter what the project might be. I can't thank you enough for the understanding that you provided  

I followed what you posted - everything works wonderfully, and because your explanation was so spot on, it all went in the code the first time without any problems at all.

<<<<<hugs>>>>>>

my pleasure Doubled - glad I could be of help


____________________________
multi-tasking - the ability to hang more than one app. at the same time.