Problem saving multiple checkboxes to table

[marvil]

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.

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.

 

[CJ_London]

It looks like your addnew and updates are outside the for each control loop - it needs to go within your if statement

For Each ctl In Form.Controls
    If ctl.ControlType = acCheckBox Then
        rstPermissions.AddNew
        rstPermissions("FK_UserID").Value = cboFullName.Value
        rstPermissions("FK_UserRoleID").Value = cboFK_UserRoleID.Value
        Select Case ctl.Name
            Case "chkAddManageEmployees":
            ...etc...
        End Select
        rstPermissions.Update
    end if
Next

 

[marvil]

Hi CJ_London

Thanks for your reply. I amended the code as per your suggestion but it now generates an error.

See the attachment.

 

[CJ_London]

Is this happening on the first, second or later interation of the loop?

It may be you need to put a rstPermissions.Close after the update

However the error implies there is something missing from your case statement - perhaps you have a checkbox control which is not covered by the case statement or a typo. I presume the FormName field is required?

Suggest you put a debug.print ctrl.name or msgbox ctrl.name in the case else part of the code and see what you get

 

[marvil]

I receive an error when I added the rstPermissions.Close after the update: Runtime error 3420 object invalid or no longer set.

I added the rstPermissions.Update to the If statement and MsgBox ctl.Name to the Case Else statement.

The MsgBox prints chkAddIssueEmpPPe which is the next checkbox in the collection but I have not added coding for it yet.

I also received runtime error 3314 You must enter a value in the tblUserPermissions.FormName field.

The required property for the FormName field in the tblUserPermissions is set to true.

I have about 124 checkboxes on the form but I only coded 4 to see if the coding works properly. Do you think I should add coding to the other checkboxes and then test the form?

 

[CJ_London]

So your error is caused because you are trying to update checkboxes which do not assign any details to your permission.

Suggest create a boolean called Processed and modify your loop as indicated in red

For Each ctl In Form.Controls
If ctl.ControlType = acCheckBox Then
Processed=True
rstPermissions.AddNew
rstPermissions("FK_UserID").Value = cboFullName.Value
rstPermissions("FK_UserRoleID").Value = cboFK_UserRoleID.Value
Select Case ctl.Name
Case "chkAddManageEmployees":
...etc...
Case Else
Processed=False
End Select
If Processed then rstPermissions.Update
end if
Next

 

[marvil]

Hi CJ_London

Thanks for your input. The data now saves to the table, but now when I click the save button again it saves the record again. I added a line rstPermissions.RecordCount > 0 to prevent duplicate records from being saved, but it doesn't seem to work. Any ideas?

[COLOR=red]If rstPermissions.RecordCount > 0 Then[/COLOR]
For Each ctl In Form.Controls
If ctl.ControlType = acCheckBox Then
[COLOR=black]Processed=True[/COLOR]
rstPermissions.AddNew
rstPermissions("FK_UserID").Value = cboFullName.Value
rstPermissions("FK_UserRoleID").Value = cboFK_UserRoleID.Value
Select Case ctl.Name
Case "chkAddManageEmployees":
...etc...
Case Else
[COLOR=black]Processed=False
End Select
If Processed then rstPermissions.Update
[/COLOR]end if
Next 
[COLOR=red]    Else
    MsgBox "The user: " & cboFullName & " already exists!"
    Exit Sub[/COLOR]
End If

 

[CJ_London]

So it is creating duplicates.

If rstPermissions.RecordCount > 0 Then

won't work unless you have run a query to populate a recordset which you haven't done. In addition it won't work where you have it, it would probably need to go in the case statement.

However I think you would be better to either disable the button once the update is complete or better still modify your tblPermissions to have a unique index across the userID and FormName fields and probably the userroleid as well.

You can do this by making these three fields the primary key or use SQL to create a unique index.

See post #4 on this link as to how to do this

http://www.access-programmers.co.uk/forums/showthread.php?p=1300975#post1300975

 

[marvil]

Thanks CJ_London

Thanks very much for your time and wisdom, you have been a great help.
I deleted the line

If rstPermissions.RecordCount > 0 Then

I added the unique index to the fields you suggested and it seems to be working fine now. I am going to merge the code with the rest of the app and test it to see if everything is working fine. I'll let you know within a day or so.

When I added the index, I wasn't sure whether Ignore Nulls should be Yes or No!

Thanks very much for your help thus far.

 

[Pat Hartman]

1. Why would you not use a bound form?

2. When referencing controls, use the Me. designation. That tells the interpreter which library defines the reference so rather than having to search all loaded libraries for cboFK_UserRoleID, it knows it can use the forms class module.

Me.cboFK_UserRoleID is more efficient than cboFK_UserRoleID.Value

.Value is the default property so it can be omitted. Or if you prefer then use,
Me.cboFK_UserRoleID.Value

 

[marvil]

Hi Pat

I managed to fix my previous code. I am now trying to run a simple select statement and I get this error: No value given for one or more parameters!

The long and short is that the user logs in. When the user selects Manage Employees on the Main Menu, the Employees form must open in Add mode if the value "chkAddManageEmployees_Add" is found in the tblUserPermissions.FormName field and in Edit mode if tblUserPermissions.FormName field contains the value "chkAddManageEmployees_Edit".

I stepped through the code, when I hover my mouse over the line

UserID = txtTempVarsUserID.Value

I get a value of UserID = 1, which is correct because I'm the Administrator and my PK_UserId is 1 and I am assigning it to FK_UserID in this line

...WHERE FK_UserID = " & UserID & "

I think the problem lies with this section of the select statement

AND FormName = " & CStr("chkAddManageEmployees_Add") & "",

Here is the code:

Private Sub lblManageEmployees_Click()
On Error GoTo lblManageEmployees_Error

Dim rst As ADODB.recordSet
Set rst = New ADODB.recordSet

UserID = txtTempVarsUserID.Value 'textbox hidden on main form and it contains the user id
rst.Open "SELECT PK_UserPermissionsID,FK_UserID,FormName,* FROM tblUserPermissions WHERE FK_UserID = " & UserID & " AND FormName = " & CStr("chkAddManageEmployees_Add") & "", Application.CurrentProject.Connection, adOpenKeyset
If rst!FormName = "chkAddManageEmployees_Add" Then
DoCmd.OpenForm "frmEmployeesManage", acNormal, , , acFormAdd, acWindowNormal
ElseIf rst!FormName = "chkAddManageEmployees_Edit" Then
DoCmd.OpenForm "frmEmployeesManage", acNormal, , , acFormEdit, acWindowNormal
End If
lblManageEmployees_Exit:
Exit Sub
lblManageEmployees_Error:
MsgBox "There was a problem when submitting the record: " & Err.Description, vbOKOnly Or vbInformation, "Record Not Submitted"
Resume lblManageEmployees_Exit
End Sub

 

[Pat Hartman]

Is the actual value in FormName a string or is it numeric? Do you have a lookup defined for this field on tblUserPermissions?

Also, your If doesn't cover all possibilities. What if the value is not chkAddManageEmployees_Add or chkAddManageEmployees_Edit?

If those are the only possible values, either remove the ELSEIF and make it just an ELSE or add a third ELSEIF that displays an error message.

 

[CJ_London]

I

wasn't sure whether Ignore Nulls should be Yes or No!

I think it should be no as previously stated
I'm now lost was to what you are trying to achieve

SELECT PK_UserPermissionsID,FK_UserID,FormName,* FROM

There is no need for the * since you are already bringing through the fields you want, so they are just being duplicated

you can just use:

Dim Rst as Recordset
Set Rst=currentdb.openrecordset("Select...")

FormName = " & CStr("chkAddManageEmployees_Add") & "",

Cstr converts a number to text so I'm not sure what you are trying to achieve here. Since you have converted it to text you should be using

FormName = '" & CStr("chkAddManageEmployees_Add") & "'"

 

[marvil]

CJ_London

Answering your statement:

I'm now lost was to what you are trying to achieve

I haven't coded in Access VBA for over 7 years, so I am trying to familiarise myself with the VBA syntax, while trying to write the program.

My client presented me with an incomplete Access database and he wants me to add & design a User Access Level Security for his application. That's why I posted some of the code here, because I needed assistance.

MS Access used to have a built-in User Access Level Security wizard if I remember correctly and for some reason Microsoft decided to exclude it from future Access releases. So now I have to create one in Access from scratch.

Have you ever written a User Access Level Security for a system? Any ideas or maybe a sample application that you know off that I can look at?

The idea is actually pretty simple as you know. The user must log in with a username and password. The user must be assigned a user role: Aministrator, CEO, Management, Sales, Data Entry etc. Each role will be assigned some permissions. So for example, if you are Management, then you can add, edit, delete and view forms, reports etc. If you have a Data Entry role then you can only add data & view data, you can not delete records etc.

 

[CJ_London]

I was really referring to my comments after.

I have written user access systems before. My data structure is based on the user access structure found in SQL Server, MySQL etc which is based on tables and actions rather than forms.

I would look to structure your table along the following lines:

tblPermissions
PermissionID autonumber PK
ProfileID long FK indexed (no duplicates) with formname
FormName Text indexed (no duplicates) with ProfileID
PAdd boolean - user can add records
PDelete boolean - user can delete records
PUpdate boolean or PEdit if you prefer - user can update changes
PView boolean - user can see the data (if false and PAdd is true, form is dataentry=true)

If you wanted to, instead of creating a two field index for ProfileID and formname, you could make them both the primary key - but keep the permissionID


tblProfiles
ProfileID autonumber PK
ProfileName text

tblUsers
UserID autonumber PK
UserName text
...
...
etc

tblAssigned
AssignID autonumber PK
UserID long FK
ProfileID long FK
DateFrom Date so you can set a record up early
DateTo Date so you can stop a user access ahead of time

Using TblAssigned you can assign a user temporary additional rights (perhaps holiday cover) or disable them for a period of time