I created a Microsoft Access database and access to the system must be controlled by User Access Level Control. The level of the user determines whether the user can add, edit, delete or view a certain form.
I created three tables which are linked via foreign keys: tblUsers, tblUserRoles and tblPermissions. (See the Tables attachment)
I designed the Permissions form to be user friendly by adding checkboxes on the form so that the Administrator can select whether a new user has Add, Edit, Delete or View rights. (See the User Level attachment)
To test my code I added a user as an Administrator. The problem is that when I select the Add, Edit, Delete and View checkboxes, it only saves the last checkbox to the Permissions table. The Administrator must have Add, Edit, Delete or View privileges on the Employees form, but now he only has View privileges. My code does not generate an error. (See the Incorrect attachment)
The Permissions table is suppose to save four entries(See the Correct attachment)
Here is my code.
Your assistance would be greatly appreciated.
I created three tables which are linked via foreign keys: tblUsers, tblUserRoles and tblPermissions. (See the Tables attachment)
I designed the Permissions form to be user friendly by adding checkboxes on the form so that the Administrator can select whether a new user has Add, Edit, Delete or View rights. (See the User Level attachment)
To test my code I added a user as an Administrator. The problem is that when I select the Add, Edit, Delete and View checkboxes, it only saves the last checkbox to the Permissions table. The Administrator must have Add, Edit, Delete or View privileges on the Employees form, but now he only has View privileges. My code does not generate an error. (See the Incorrect attachment)
The Permissions table is suppose to save four entries(See the Correct attachment)
Here is my code.
Code:
Private Sub cmdSave_Click()
Dim rstPermissions As Object
Dim dbFSManagement As Object
Set dbFSManagement = CurrentDb
Set rstPermissions = New ADODB.recordSet
Set rstPermissions = dbFSManagement.OpenRecordset("tblUserPermissions")
rstPermissions.AddNew
rstPermissions("FK_UserID").Value = cboFullName.Value
rstPermissions("FK_UserRoleID").Value = cboFK_UserRoleID.Value
For Each ctl In Form.Controls
If ctl.ControlType = acCheckBox Then
Select Case ctl.Name
Case "chkAddManageEmployees":
If chkAddManageEmployees.Value = True Then
rstPermissions("FormName").Value = ctl.Name & CStr("_Add")
rstPermissions("Allowed").Value = "Yes"
Else
rstPermissions("FormName").Value = ctl.Name & CStr("_NotSelected")
rstPermissions("Allowed").Value = "No"
End If
Case "chkEditManageEmployees":
If chkEditManageEmployees.Value = True Then
rstPermissions("FormName").Value = ctl.Name & CStr("_Edit")
rstPermissions("Allowed").Value = "Yes"
Else
rstPermissions("FormName").Value = ctl.Name & CStr("_NotSelected")
rstPermissions("Allowed").Value = "No"
End If
Case "chkDeleteManageEmployees":
If chkDeleteManageEmployees.Value = True Then
rstPermissions("FormName").Value = ctl.Name & CStr("_Delete")
rstPermissions("Allowed").Value = "Yes"
Else
rstPermissions("FormName").Value = ctl.Name & CStr("_NotSelected")
rstPermissions("Allowed").Value = "No"
End If
Case "chkViewManageEmployees":
If chkViewManageEmployees.Value = True Then
rstPermissions("FormName").Value = ctl.Name & CStr("_View")
rstPermissions("Allowed").Value = "Yes"
Else
rstPermissions("FormName").Value = ctl.Name & CStr("_NotSelected")
rstPermissions("Allowed").Value = "No"
End If
Case Else
End Select
End If
Next
rstPermissions.Update
Set rstPermissions = Nothing
Set dbFSManagement = Nothing
End Sub
Your assistance would be greatly appreciated.