I've read the recent threads on user-level access and implemented a few things, but I'm stuck on other portions. I think most of it is a lack of understanding and lack of experience with code rather than a coding error. My database needs are surprisingly similar to the Firefighter database here:https://www.accessforums.net/showthr...ecific-records, but again, my understanding isn't at the same level as that user.
I've got a login form similar to this Datapig tutorial: http://www.datapigtechnologies.com/f...loginform.html
The "Authorized" table holds:
- USERID (key)
- PWD
- a lookup to the employee's name in the employee data table (not sure if this is necessary, but I thought it might be useful for access levels)
- a combobox to choose the attributes/access level (Admin, Developer, Manager, User), field name: Attributes
Some of the examples have had the access levels in a separate table, and each entry in the login/Authorized table is linked to a level in the separate table. What are the benefits of doing it in a separate table as opposed to doing the combobox?
The two main goals I have for the login authentication is to:
- Hide buttons on the landing page and all other forms depending on access level
- Filter results of a subform based on access level
For the first part, my approach was to capture the USERID inputted in the login form, and put it in a hidden textbox on the landing page that the a successful login takes you to (frmOwners). I thought the best way to hide the buttons would be to to a DLOOKUP of the Attributes field in the Authorized table based on the USERID, and set buttons to hidden or visible based on a Select Case of the result. I don't have any of that code working, that was simply the theory. I ran into my first problem when I couldn't get a MsgBox to display showing the result of the DLOOKUP when I used code like the following:
I don't fully understand the Form_Load and Form_Open, but I'm not sure where to look for an appropriate explanation.Code:Private Sub Form_Load() Dim myword As String myword = DLookup("Attributes", "Authorized", "USERID = '" & Me.txtUserID.Value & "'") MsgBox (myword) End Sub
For the second part, the frmOwners has a combobox that links to employee names in the tblOwners. The tblResourceAllocation has a field that looks up the names in the tblOwners for a one to many relationship. Therefore, I have a subform on frmOwners that displays all the tblResourceAllocation records tied to that particular owner when his name is selected in the drop down. What I want to do is restrict the names in the drop down form based on the access level of the user:
- If Admin or Developer, show all choices
- If Manager, only show that manager's name in the drop down and all the records tied to him in the subform
- If User, don't show frmOwners, and take them to a report instead.
To implement this, the code would need to be on the login form, correct? Would it be a case statement with the Dlookup result of the attributes, and then specify a Where condition in the DoCmd.OpenForm for the Managers but open a report for the Users? This seems like the easier of my two issues, but I'm stuck on the first at the moment.
I've followed a lot of the links that June7 has provided in the past, but feel free to link them again if I glossed over a solution. Thanks!