Long story short, the problem is that each "user" who accesses the database needs different levels of access to various forms, etc, and their name, etc needs to be stored in a way that it can be accessed by any form/report during their session.
I had this working well with global variables until recently, and hours of internet searching when this failed have revealed this is a bad way to do this! I have now created a table "tblVariables" to store this information temporarily, and I'm after the VBA code allowing me to access the cell contents, and load it into a variable for use and manipulation on the form, then update/store it back in the cell when closing the form.
I would really appreciate some help in finishing this off so that it works! For simplicity, there's a table of users (tblusers) with "username", "password", "accesslevel", "firstname", "surname", "fullname" (first name + last name); another table (tblVariables) stores the global variables for the session in case there's an error and they get reset. My current code looks something like below, but is not working! The immediate window on the login form shows the correct variables, but as soon as it gets to the main menu, all the variables are lost, and nothing is updated in the table!
Expand|Select|Wrap|Line Numbers
- Public Sub VariablesUpdate()
- on error goto ErrorHandler
- dim rstVariables as recordset
- set rstvariables = currentdb.openrecordset("tblVariables")
- with rstvariables
- .movefirst
- !CurrentUN = gstrUN
- !CurrentUFN = gstrUFN
- !CurrentAL = gstrAL
- !CurrentSU = gstrSU
- end with
- rstvariables.close
- Errorhandler:
- End Sub