Help!!! Standard Module & GetGlobal

[rlm]

I'm using MSAccess 2007. I have a Table called: Personnel and a field called [AccessLevel]. I'm trying to set the numeric value within the [AccessLevel] field as a Public Variant so I can call it later in the application. Could you please give me some help. I have copied my Standard Module Code below:

Option Compare Database
Option Explicit
Public glbAccessLevel As Variant
Public Function GetGlobal(VariableType As String) As Variant
Select Case VariableType
Case "AccessLevel"
GetGlobal = glbAccessLevel
End Select
End Function

Also I need to know how to recall the Public Variable within a form and VBA.

Thanks for your time and assistance in advance.....

 

[tehNellie]

Global Variables like this, especially as a variant are bad, bad things to have.

Ignoring the risks from a code management perspective, when you come back in 6 months time to do some more stuff to your db you'll have no idea what data goes in it or where in the code uses it though the name at least might give you an inkling. you're also writing an application in Access, something far better designed to store data on the off chance you need it again than a global variable.

If you think you might need it several times, write a proper function that grabs the data direct from source (the table) and just call that function when you need it

Public Function ReturnAccessLevel([i]byRef somecriteria as <type>[/i]) as string
  dim db as dao.database
  dim rs as dao.recordset

  'set the db object to this database.
  set db = currentdb
  
  'open the query in a recordset
  set rs = db.openrecordset("SELECT AccessLevel FROM personnel [i]WHERE somecriteria if you need it[/i])

  'make sure we have a record 
  if not rs.EOF
  
    'set the value
    ReturnAccessLevel = rs![AccessLevel]
  
   else
 
    'No Record in the db so send a zero length string
    ReturnAccessLevel = ""

  end if 
  
    'tidyup
    rs.close
    set rs = nothing
    set db = nothing
End Function

That's a pretty basic function knocked up but it's far more reliable and flexible than trying to manage a global variable of a variant datatype to hold a value that looks like it might be criteria dependent.

I dare say that there are other ways you could achieve the same result depending on how you were going to do it but from a VBA perspective using that function to populate a text box (for example) is along the lines of:
(it's late and I can't remember if you can populate the text box by directly calling the function.)

Private Sub SomethingorOther
  dim strAccessLevel as String

  strAccessLevel = ReturnAccessLevel([i]some criteria[/i])

  me.txtbox.value = strAccessLevel

End sub

 

[rlm]

Thanks. I'm going to try this, it does look pretty basic. As for recalling it, I'm going to use it with an IF statement prior to opening a form from a Control Button. Looking at something like this:
If strAccessLevel <= 2 Then
DoCmd.Close
DoCmd.OpenForm "Name of the Form to Access"
Else
MsgBox "Sorry You Don't Have The Access Level. If in Error, Contact Administrator"
DoCmd.Close
DoCmd.OpenForm "Name of the Form to Return To"
End If

 

[rlm]

I took your idea and entered it. Below is a copy of the New Standard Module. Does everything seem correct because when I try to retrieve the Access Level it gives me an error.
Standard Module:
Public Function ReturnAccessLevel(ALevel) As String
Dim db As dao.Database
Dim rs As dao.Recordset
'set the db object to this database.
Set db = CurrentDb
'open the query in a recordset
'set rs = db.openrecordset("SELECT AccessLevel FROM personnel WHERE somecriteria if you need it)
Set rs = db.OpenRecordset("SELECT AccessLevel FROM personnel")
'make sure we have a record
If Not rs.EOF Then
'set the value
ReturnAccessLevel = rs![AccessLevel]
Else
'No Record in the db so send a zero length string
ReturnAccessLevel = ""
End If

Form (Log In) VBA Code:
Private Sub Log_In_Validate_Button_Click()
Me.Filter = "LogIn = [Forms]![Log In]![UserName] And Password = [Forms]![Log In]![Password]"
Me.FilterOn = True
Dim LTotal As Long
LTotal = DCount("LogIn", "Personnel", "LogIn = [Forms]![Log In]![UserName] And Password = [Forms]![Log In]![Password]")

If LTotal = 1 Then
MsgBox "Access Granted", vbInformation, "Maintenance Department"

Dim strAccessLevel As String
strAccessLevel = ReturnAccessLevel
'This is a test to see if I can retrieve the AccessLevel
MsgBox ReturnAccessLevel

DoCmd.Close
DoCmd.OpenForm "Home Page"
Else
MsgBox "Please Re-Enter Your User Name and Password", vbInformation, "OOPS LOOKS LIKE WE HAVE A PROBLEM"
DoCmd.Close
DoCmd.OpenForm "Log In"
End If
End Sub

Error (Hangs Up on ReturnAccessLevel)
---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Argument not optional
---------------------------
OK Help
---------------------------

Thanks again for your help.

 

[VNVfan]

Is it because in your function you have declared an argument (ALevel) and when you're calling the function you're not providing it?

I.E. When you're calling the function you need to put "ReturnAccessLevel(<somethinghere>).

It doesn't look like the ALevel argument is actually used in the function, so if you rename the function without it (I.E. Public Function ReturnAccessLevel() As String) it may work.

 

[tehNellie]

Looks like it to me. rlm, when you're posting code back, can you please use the [ code] tags in the post it makes it so much easier to read. Ta.

 

[rlm]

Thanks to both of you. I took out the ALevel and just made it ReturnAccessLevel() and it worked. But now my problem is how can I have it reset when another person logs in and make the ReturnAccessLevel() reflect their access level?

 

[VNVfan]

I'm sure I'll be corrected if I'm wrong - I'm no pro - but I think this is where you'll want an argument as part of the function name, and then use that argument in the SQL statement of the recordset.

For example, you'll want the function name to be something like Public Function ReturnAccessLevel(UserName As String) As String) and the recordset to be something like Set rs = db.OpenRecordset("SELECT AccessLevel FROM personnel WHERE personnel.<yourusernamefield> = UserName".

Then when you call the function in your code, you'll call it with the argument and it'll go into the table, search for the username (or however you're going to identify your users) and return the user's access level if it finds and match or a zero-length string if it doesn't.

 

[Rank Am]

Trying to emulate user and group security with VBA in accdb/e which doesn't support this is going to be a lot of hard work and probably not going to work. Your best bet would be to have an authorisation class module,use the windows logonuser api call which is what we do or use an MDB database not accdb

Unless you specifically require some of the additional functionality found in an accdb you will find it easier to implement user and group security by saving the database as Access 2003 MDB file which supports this and can be specified for MDB files from Access 2007.

However this is still not that easy to set up, but a damn site easier than what you are proposing and a bit more secure - storing database passwords and user names in tables in the db is not a great idea.
http://www.access-programmers.co.uk/forums/showthread.php?t=154137 gives a good intro to this
Access 2007 also doesn't support replication which is a pain in a multiuser / site environment.
We run our maintenance management system using an accdb and we use the logonUser API call, to get the windows user name they then enter the database encryption password. So the db User is always who is logged onto the windows domain. Users are a assigned read/only, append and edit rights As the db is multiuser the backends tables and queries are all completely disconnected from the front ends and all data transfer is handled by ADODB Recordsets.
We handle data management and integrity through good design and business logic modelling:
a) Don't bind forms directly to tables but use paramaterised queries
b) All recordsets that return large amounts if records for analysis shall be read only
c) Once a process is finished and approved by a manager the record shall be locked and become read only
d) Permissions are set against assets in the asset register to append, edit, approve
etc etc
the whole lot is contained within class modules - the only code in the forms is basically there to initiate the classes from events an point the recordsets at controls
This is not perfect but it kind of works, I wanted to use SQL server which is much more secure and a lot easier to set up permissions at a table level than access. Management said no to the moderate price tag for operations and maintenance management. They did spend gazillions without a second thought on our Oracle Bean Counting application

 

[nanscombe]

I've put together a little database. It's very basic but it might be of some use to you.

UserNames / Passwords are in the table Personnel.

Feel free to have a look around and pinch any ideas.

 

[nanscombe]

An updated, slightly better version...

 

[rlm]

Thanks for all your help. Nanscombe I took your coding and it works just like it suspose to. Thanks a MILLION.........

 

[nanscombe]

Good. I'm glad it worked Ok for you.