User Startup Script

[csoseman]

I have a table with the fields userName and dbPermissions. the username in the database is the same one that is displayed when calling the Environ$("username") function. I want to build a script that will find record where the function matches the userName in the table and then looks up that permission. After that I will write code to open certain forms and disable certain fields in those forms, which I think I can handle. The question I have is are there any good discussions on this or would someone be able to help me with the script for the cycling through and checking the userName against the Environ function and then looking up that users permissions?

 

[CBrighton]

A simple DLookup willreturn the contents of a field based on set criteria.

e.g.

DLookup("dbPermissions","tblUsers","userName = '" & Environ$("username")

Note that while I have a function to pull the username I just used what you typed in this example, if you have a custom fuinction to get the name then use that.

Further info: http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[Simon_MT]

I store this values on the Start-up Menu.

Function GetUser()
Dim db As DAO.Database
Dim rs As DAO.Recordset

    With CodeContextObject
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT EmployeesLookup.Employee, EmployeesLookup.[Employee Login], EmployeesLookup.[Employee Inv Approval] FROM EmployeesLookup WHERE EmployeesLookup.[Employee Login] = '" & GetWinUserName & "'")
        Do
            .UserDB = rs!Employee
            .UserInv = rs![Employee Inv Approval]
            rs.MoveNext
        Loop Until rs.EOF
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End With
End Function

Function GetWinUserName()
    GetWinUserName = VBA.Environ("UserName")
End Function

Simon

 

[vbaInet]

        Do
            .UserDB = rs!Employee
            .UserInv = rs![Employee Inv Approval]
            rs.MoveNext
        Loop Until rs.EOF

Simon

I would be careful with the loop there because it will fail if the recordset is empty. The norm is Do While Not rst.EOF.

@csoseman: It all depends on the level of permissions and what you want to control.

 

[csoseman]

I would be careful with the loop there because it will fail if the recordset is empty. The norm is Do While Not rst.EOF.

@csoseman: It all depends on the level of permissions and what you want to control.

@vbaInet I have 4 different user permissions. Eventually I want to be able to call my custom ribbons and adjust specific splash screens for each permission level.

 

[vbaInet]

How many adjustments will there be?

 

[csoseman]

I would have 3 different ribbons depending on the permissions of the user and a different form would open up with certain filters depending on the user.

 

[vbaInet]

Have you seen the way group permissions are handled in pre Access 2007?

 

[csoseman]

Have you seen the way group permissions are handled in pre Access 2007?

I have. I really don't like the way it's set up and odds are that I will end up converting to 2007 by the end of the year.

 

[vbaInet]

Then create some simple tables: Groups table and Groups-to-permissions table.

Groups:
GroupID
GroupName

GroupPermissions:
GroupID
RibbonID
Permission

Something like that.

 

[csoseman]

Then create some simple tables: Groups table and Groups-to-permissions table.

Groups:
GroupID
GroupName

GroupPermissions:
GroupID
RibbonID
Permission

Something like that.

That's where I'm at right now just have to get these ribbons made up. The ribbon will be dependent on the permissions ID.

 

[vbaInet]

Ok, and you can use the DLookup() from CBrighton to check the access level.

Happy coding!