checkboxes not saving to table

M

marvil

Registered User.
Local time
Today, 23:14
Joined
Oct 16, 2013
Messages
24
I am writing a program in Access 2010. My check boxes are not saving to the table. I have stepped through the code with no errors and it only saves the last check box to the table. Here is my code:

Private Sub cmdSave_Click()
On Error GoTo cmdSave_Click_Error

Dim rs As Object
Dim db As Object

Set db = CurrentDb
Set rs = db.OpenRecordset("tblUserPermissions")

rs.AddNew
rs!FK_UserID = cboFullName.Value
rs!FK_UserRoleID = cboFK_UserRoleID.Value

If (chkAddManageEmployees.Value = True) And (chkEditManageEmployees.Value = False) And (chkDeleteManageEmployees.Value = False) And (chkViewManageEmployees.Value = False) Then
rs!FormName = "ManageEmployees"
rs!Allowed = "Add"
Else
rs!FormName = "ManageEmployees"
rs!Allowed = "None"
End If

If (chkAddManageEmployees.Value = True) And (chkEditManageEmployees.Value = True) And (chkDeleteManageEmployees.Value = True) And (chkViewManageEmployees.Value = True) Then
rs!FormName = "ManageEmployees"
rs!Allowed = "Edit"
Else
rs!FormName = "ManageEmployees"
rs!Allowed = "None"
End If
.......

If chkViewFenceTypes.Value = True Then
rs!FormName = "FenceTypes"
rs!Allowed = "View"
Else
rs!FormName = "FenceTypes"
rs!Allowed = "None"
End If

If chkViewMaterialTypes.Value = True Then
rs!FormName = "MaterialTypes"
rs!Allowed = "View"
Else
rs!FormName = "MaterialTypes"
rs!Allowed = "None"
End If

If chkViewIdentificationTypes.Value = True Then
rs!FormName = "IdentificationTypes"
rs!Allowed = "View"
Else
rs!FormName = "IdentificationTypes"
rs!Allowed = "None"
End If

If chkViewInstallationTeams.Value = True Then
rs!FormName = "InstallationTeams"
rs!Allowed = "View"
Else
rs!FormName = "InstallationTeams"
rs!Allowed = "None"
End If
rs.Update
Exit Sub

cmdSave_Click_Exit:
rs.Close
Set rs = Nothing
db.Close
Exit Sub

cmdSave_Click_Error:
MsgBox "Error #:" & Err.Number & vbCrLf & "Error Description: " & Err.Description, vbOKCancel, "User Permissions: Duplicate values not allowed!"
Resume cmdSave_Click_Exit

End Sub
Click to expand...

I attached the full code.
 
marvil said:
I am writing a program in Access 2010. My check boxes are not saving to the table. I have stepped through the code with no errors and it only saves the last check box to the table. Here is my code:
Click to expand...
Yes because you are storing all the check boxes in the same 2 table fields, there for only the last one is saved.
The 2 fields are:
Code: 
FormName = "ManageEmployees"
Allowed = "Edit"
If you want to add a new record/row in the table for each check boxes, then you need an Add and Update per check box.

Code: 
...
rs.AddNew
If chkViewFenceTypes.Value = True Then
rs!FormName = "FenceTypes"
rs!Allowed = "View"
Else
rs!FormName = "FenceTypes"
rs!Allowed = "None"
End If
rs.Update
rs.AddNew
If chkViewMaterialTypes.Value = True Then
rs!FormName = "MaterialTypes"
rs!Allowed = "View"
Else
rs!FormName = "MaterialTypes"
rs!Allowed = "None"
End If
rs.Update
....
 
I added and update per check box as you said, but I'm getting an error:
Error 3314: You must enter a value in the tblUserPermissions.FK_UserID field. What about the two combo boxes at the beginning of the code:

rs!FK_UserID = cboFullName.Value
rs!FK_UserRoleID = cboFK_UserRoleID.Value
Click to expand...

I amended my code and now it looks like this
Private Sub cmdSave_Click()
On Error GoTo cmdSave_Click_Error

Dim rs As Object
Dim db As Object

Set db = CurrentDb
Set rs = db.OpenRecordset("tblUserPermissions")

rs.AddNew
rs!FK_UserID = cboFullName.Value
rs!FK_UserRoleID = cboFK_UserRoleID.Value

rs.AddNew
If (chkAddManageEmployees.Value = True) And (chkEditManageEmployees.Value = False) And (chkDeleteManageEmployees.Value = False) And (chkViewManageEmployees.Value = False) Then
rs!FormName = "ManageEmployees"
rs!Allowed = "Add"
Else
rs!FormName = "ManageEmployees"
rs!Allowed = "None"
End If
​
rs.Update
rs.AddNew
If (chkAddManageEmployees.Value = True) And (chkEditManageEmployees.Value = True) And (chkDeleteManageEmployees.Value = True) And (chkViewManageEmployees.Value = True) Then
rs!FormName = "ManageEmployees"
rs!Allowed = "Edit"
Else
rs!FormName = "ManageEmployees"
rs!Allowed = "None"
End If
​
rs.Update
​
.....
rs.AddNew
If chkViewIdentificationTypes.Value = True Then
rs!FormName = "IdentificationTypes"
rs!Allowed = "View"
Else
rs!FormName = "IdentificationTypes"
rs!Allowed = "None"
End If
​
rs.Update
rs.AddNew
If chkViewInstallationTeams.Value = True Then
rs!FormName = "InstallationTeams"
rs!Allowed = "View"
Else
rs!FormName = "InstallationTeams"
rs!Allowed = "None"
End If
​
rs.Update
​
rs.Update
Set rs = Nothing
Set db = Nothing

cmdSave_Click_Exit:
Exit Sub

cmdSave_Click_Error:
MsgBox "Error #:" & Err.Number & vbCrLf & "Error Description: " & Err.Description, vbOKCancel, "User Permissions: Duplicate values not allowed!"
Resume cmdSave_Click_Exit
End Sub
Click to expand...
 
Your CODE logic seems to be a bit off..
Code: 
Private Sub cmdSave_Click()
On Error GoTo cmdSave_Click_Error

Dim rs As Object
Dim db As Object

Set db = CurrentDb
Set rs = db.OpenRecordset("tblUserPermissions")
    rs.AddNew
        [COLOR=Red]rs!FK_UserID = cboFullName.Value
        rs!FK_UserRoleID = cboFK_UserRoleID.Value[/COLOR]
        If (chkAddManageEmployees.Value = True) And (chkEditManageEmployees.Value = False) And (chkDeleteManageEmployees.Value = False) And (chkViewManageEmployees.Value = False) Then
            rs!FormName = "ManageEmployees"
            rs!Allowed = "Add"
        Else
            rs!FormName = "ManageEmployees"
            rs!Allowed = "None"
        End If

    rs.Update
    rs.AddNew
       [COLOR=Red] rs!FK_UserID = cboFullName.Value
        rs!FK_UserRoleID = cboFK_UserRoleID.Value[/COLOR]
        If (chkAddManageEmployees.Value = True) And (chkEditManageEmployees.Value = True) And (chkDeleteManageEmployees.Value = True) And (chkViewManageEmployees.Value = True) Then
            rs!FormName = "ManageEmployees"
            rs!Allowed = "Edit"
        Else
            rs!FormName = "ManageEmployees"
            rs!Allowed = "None"
        End If

    rs.Update

.....

    rs.AddNew
       [COLOR=Red] rs!FK_UserID = cboFullName.Value
        rs!FK_UserRoleID = cboFK_UserRoleID.Value[/COLOR]
        If chkViewIdentificationTypes.Value = True Then
            rs!FormName = "IdentificationTypes"
            rs!Allowed = "View"
        Else
            rs!FormName = "IdentificationTypes"
            rs!Allowed = "None"
        End If

    rs.Update
    rs.AddNew
        [COLOR=Red]rs!FK_UserID = cboFullName.Value
        rs!FK_UserRoleID = cboFK_UserRoleID.Value[/COLOR]
        If chkViewInstallationTeams.Value = True Then
            rs!FormName = "InstallationTeams"
            rs!Allowed = "View"
        Else
            rs!FormName = "InstallationTeams"
            rs!Allowed = "None"
        End If

    rs.Update
    
Set rs = Nothing
Set db = Nothing

cmdSave_Click_Exit:
Exit Sub

cmdSave_Click_Error:
MsgBox "Error #:" & Err.Number & vbCrLf & "Error Description: " & Err.Description, vbOKCancel, "User Permissions: Duplicate values not allowed!"
Resume cmdSave_Click_Exit
End Sub
The FK and UserID should be added for each record.
 
I added the code as you suggested but I am getting an new error: Error 3022 complaining about duplicate records, because I added a unique index on FK_UserID, FK_UserRoleID and FormName.

It only saved the two lines in the first else-statement:
rs!FormName = "ManageEmployees"
rs!Allowed = "None"
Click to expand...

Private Sub cmdSave_Click()
On Error GoTo cmdSave_Click_Error

Dim rs As Object
Dim db As Object

Set db = CurrentDb
Set rs = db.OpenRecordset("tblUserPermissions")
rs.AddNew
rs!FK_UserID = cboFullName.Value
rs!FK_UserRoleID = cboFK_UserRoleID.Value
If (chkAddManageEmployees.Value = True) And (chkEditManageEmployees.Value = False) And (chkDeleteManageEmployees.Value = False) And (chkViewManageEmployees.Value = False) Then
rs!FormName = "ManageEmployees"
rs!Allowed = "Add"
Else
rs!FormName = "ManageEmployees"
rs!Allowed = "None"
End If
Click to expand...
 
If UserID is a Primary Key/Indexed with No duplicates, then there is no way around it..

Any reason why they are all Indexed with No duplicates? This now worries me more about the table structure.
 
Please see the table structure and indexes in the attachment. I don't want users to save duplicate records that's why I added the indexes. Should "Ignore Nulls" be Yes or No?
 

Attachments

  • Unique Index.png
    Unique Index.png
    40.2 KB · Views: 104
If the UserID and UserRoleID are Foreign Keys then (in most cases) they cannot be UNIQUE. Change the Unique value to No. Then try the code again.
 
I removed the index from the three fields and it now saves to the table - See the attachment "ResultNow".

Can I leave out the 'else-statements'? Because the result I actually want is in the attachment: "Result I want"?
 

Attachments

  • ResultNow.png
    ResultNow.png
    14.2 KB · Views: 110
  • Result I want.png
    Result I want.png
    11.5 KB · Views: 111
Are you by any chance using lookups in your tblpermissions, thinking specifically FK_UserRoleID? If so, this is probably causing your problem. Remove them and see if things are clearer.

I agree with Paul, the 3 fields should each be indexed, duplicates allowed, but the idxPermissions should be set to unique. With regards nulls, I think try it both ways, my instinct says set to no - don't forget this is excluding from the index, not the table.

There is some other confusion, your allow edits says it will be either yes or no, but it is populated with none and edit. In itself this does not affect the indexing, but I just wonder if the thinking may have changed and needs to be accounted for in your code
 
The indexes actually caused more problems. The only reason I added them was to prevent a user from adding the same record twice. Sometimes a user's mind wonders a bit and then he/she cannot remember whether they have already saved the record - See the attachment "ResultNow".

The result I actually want is in the attachment: "Result I want"?
 
List all possible cases.. You have shown only 2..
 
Sorry struggling a bit to find the correct format. I exported the query to Word, Excel but the uploader is complaining about the file extension
 
So have you deleted the records you don't require before setting the index? If you don't you won't be able to set the index.

Also, in your required index, you have for records 1358 and 1359 which have the same values for the index.

Looks to me like you need another field called say Action which has values one of Edit/View/Delete etc which is also part of your index - so over 4 fields and change your allowed field to a yes/no field
 
It was a typo I made in my If statement, I corrected it and thank you very much I clearly missed it.
Code: 
you have for records 1358 and 1359 which

Is it possible to leave out the else statements? I only want to write Add, Edit or View in the Allowed field and exclude the "None".

I removed the indexes completely because it just created more frustration. I am not used to coding in VBA.
 
You must log in or register to reply here.

Similar threads

N
How to ensure that the current stock control is update when the data appears on the subform in access
Replies
5
Views
425
Minty
Minty
L
Consuming an API to retrieve data and store it in a Microsoft Access table.
Replies
3
Views
1,071
lbcurrier
L
C
Working in 32 bit but not in 64.
Replies
19
Views
1,910
Catalina
C
mloucel
Solved SubForm [Continuous Form] not updating data.
Replies
18
Views
2,028
mloucel
mloucel
R
manipulating checkboxes on pdf in ms access vba
Replies
10
Views
2,326
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom