Global Variables

[kingsgambit]

Can anybody show me how to setup a global variable. I have a logon form the security level needs to be the varible.
How do you write a query which will check the global variablr against a record that has a security level.
i.e if they match open the record if not Msg "no access"

I have asked this question before and people have suggested access security.
But I would like to know how to do this coding, I don't know much about coding so simple steps would help

 

[Pat Hartman]

Here's a piece of code that runs a parameter query to check authorization. You can substitute your variable for the literal "AUTHORIZ" in my sample. Of course you'd need to change the other names as appropriate.

Private Sub Form_Open(Cancel As Integer)
Dim WgtDB As DAO.Database
Dim strSQL As String
Dim QD1 As DAO.QueryDef
Dim TempSet1 As DAO.Recordset
Set WgtDB = CodeDb
Set QD1 = WgtDB.QueryDefs!QCheckAuthorization
QD1.Parameters![UserId] = gUserID
QD1.Parameters![AuthType] = "AUTHORIZ"
Set TempSet1 = QD1.OpenRecordset
If TempSet1.EOF Then
MsgBox "You are not authorized for this function", vbOKOnly
Cancel = True
End If
End Sub


PARAMETERS UserID Text, AuthType Text;
SELECT A.USER_ID, A.AUTH_TYP
FROM DBO_USER_AUTH AS A
WHERE ((([A].[USER_ID])=[UserID]) AND (([A].[AUTH_TYP])=[AuthType]
Or ([A].[AUTH_TYP])="SUPPORT"));

 

[David Mack]

I do not know how your logon form works, but this may help you get on with your project. You need to have a table with the person's name and another field called "strSecurityLevel." Base your logon form on this table. However you validate the person is up to you but I would use the logon form to store his name and strSecurityLevel. When you have validated the user, hide the form but leave it open. This is a great way to set up a "global."

Now, your user must be opening up another form based on a query which now shows All records regardless of security level. What you need to do is go into design mode of that query, let's call it qryData. Somewhere in qryData you have a column with each record's security level. Let's call that column "Clearance". In the Criteria row of the QBE under "Clearance", you need to put your filter which will only pass records of this user's security level. Pump the value of the txt field on your hidden logon form, strSecurityLevel, into the query Criteria row like this:

Like Forms![frmLogon]![txtSecurityLevel]

I'd recommend having a numercial security level instead of "Secret", "Confidential", etc. If you set the clearance of a record at Confidential, a person with secret clearance would not be able to view it. Try a 1, 2, 3, 4, 5(highest level) and then have the the filter look a little more like:

<= Forms![frmLogon]![txtSecurityLevel]

This way, a person with a security level of 3 can see info in level 1, 2 and 3...but not 4 and 5.

Good luck,

Cheers,

Dave