Code for using Checkbox value to write to associative table

[sneuberg]

The current approach requires you to add twenty six check boxes to the form and keep them consistent with the tblClassifications table for externity. This is no way to live. I suggest using the tblCllassifications as a staging area for this functionality. First you add a Yes/No field to tblCllassifications. You then make a continuous form of the the table and put it in a subform on the Incident Data Form (frmNewMain). Then you put code in the form's after update and current events to make the tblIncidentClassifications table match what the user did in the subform.

If you have or will be going to a multiuser system this won't work and the approach would be change to have a local copy of the tblCllassifications table, but if you have a multiuser system even the current approach has problems that need to be addressed.

Let's me know if you like my suggested approach. If you do I'll set up a prototype.

 

[tbbrown32]

While I do like the thought of not having to ensure that the checkboxes are continually consistent with the other parts of the database, at this point it'll be a multi-user database with the tables housed in a shared folder as the back end and then a copy of the forms locally for each user as the front end.

 

[pbaldy]

While I do like the thought of not having to ensure that the checkboxes are continually consistent with the other parts of the database

Brings me back to the multiselect listbox option.

 

[sneuberg]

Then you would need to have a copy of the table(s) on the frontend. It still seems like it would be easier to maintain. Would you like me to create a prototype of this or would you like some code to do the updates from the check boxes as you currently have them, i.e., code for the after update event.

Since you have a multiuser database I think this needs to be done as a transaction so that if two users are updating the same record at the the same time only one of the users' inputs is applied; not part from one and part from the other. Ideally the user would be notified that another user had modified the record as it does when fields are bound to the table but I don't know how to get that affect when doing this sort of bulk update thing. I suggest posting a question for this.

 

[tbbrown32]

Code for the updates from the check boxes (code for the after update event) would be great! Being able to do both inserts and deletes from the checkboxes without having to manually delete from the tables would be really helpful!

 

[sneuberg]

Here's a subroutine that updates the tblIncidentClassifications table so that it matches the states of the checkboxes. You could put this in the form's afterupdate event but that wouldn't be enough because if the user only changes these check boxes and doesn't change other fields that doesn't fire. So you could call it from the click events of the next, previous, close, home, etc or just put a button on the form to update the check boxes.

You could have code to update whenever the user changes a check box but that would require adding a click event for all of the checkboxes and I suspect you were trying to avoid that.

I'm still trying to figure out the case when the user adds a new record. At the moment I'm having problems adding a new record even without any new code. Also note this code is only for two checkboxes. You need to change 2 to 26 or whatever.

Private Sub UpdateIncidents()

Dim ws As DAO.Workspace
Dim x As Integer
If Me.NewRecord Then
    Exit Sub
End If

Set ws = DBEngine(0)
DBEngine.Idle dbRefreshCache         ' refresh read cache
ws.BeginTrans
For x = 1 To 2
    If DCount("*", "tblIncidentClassifications", "InternalIncidentID = '" & Me.InternalIncidentID & "' AND  ClassificationID = " & x) = 0 Then
        If Me.Controls("Chk" & x) = True Then
            CurrentDb.Execute "INSERT INTO tblIncidentClassifications(InternalIncidentID, ClassificationID) VALUES('" & Me.InternalIncidentID & "', " & x & ")", dbFailOnError
        End If
    Else
        If Me.Controls("Chk" & x) = False Then
            CurrentDb.Execute "DELETE FROM tblIncidentClassifications WHERE InternalIncidentID = '" & Me.InternalIncidentID & "' AND  ClassificationID = " & x, dbFailOnError
        End If
    End If
Next x
ws.CommitTrans dbForceOSFlush        ' flush the lazy-write cache

End Sub

I'm not sure if this transaction stuff really works in this context. You might want to post a question about that too. Note also that this code only loops through two checkboxes.

 

[tbbrown32]

This works really well! I've tied the code to a button labeled "Save" so now when I click save it will insert or delete accordingly. The new record button is also working, and it clears out any all checkboxes.

(The Code Below):Since I need to do this on 4 of the tabs, I've begun adding in new code to what you gave me for the next tab that I need to complete. It is working so far, my only question is do I create a brand new Private Sub or can I work off the existing code and insert my other variable in there? It will be inserting into a different table, so I'm assuming I create a new Private Sub, but can I use Form_Current twice?

Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim x As Integer
For x = 1 To 26
Me.Controls("Chk" & x) = False
Next x
Set db = CurrentDb
strSQL = "SELECT ClassificationID FROM tblIncidentClassifications WHERE tblIncidentClassifications.InternalIncidentID = '" & Me.InternalIncidentID & "'"
Set rs = db.OpenRecordset(strSQL)
Do While Not rs.EOF
Me.Controls("Chk" & rs!ClassificationID) = True
rs.MoveNext
Loop
rs.Close
db.Close

End Sub

 

[sneuberg]

I suggest take this code out of the Form_Current and put it in its own subroutine. Some like

Private Sub UpdateIncidentCheckBoxes()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim x As Integer
For x = 1 To 26
Me.Controls("Chk" & x) = False
Next x
Set db = CurrentDb
strSQL = "SELECT ClassificationID FROM tblIncidentClassifications WHERE tblIncidentClassifications.InternalIncidentID = '" & Me.InternalIncidentID & "'"
Set rs = db.OpenRecordset(strSQL)
Do While Not rs.EOF
Me.Controls("Chk" & rs!ClassificationID) = True
rs.MoveNext
Loop
rs.Close
db.Close

End Sub

And then call it in the form current like:

Private Sub Form_Current()

UpdateIncidentCheckBoxes

End Sub

Then make other subroutines for the other tabs and call them in Form_Current the same way. Note that you will have to give you check boxes different names for the other tabs. So maybe ChkA1, ChkA2 etc for Actions; ChkR1, ChkR2 for Referrals.

I guess you could make the subroutine generic and pass it the parameters that make it different, but as I count at least four I'd just copy and paste and make the modifications.

 

[tbbrown32]

Thanks! I've completed 2 tabs and they're working perfectly! Thanks so much for the assistance!

 

[sneuberg]

Just curious. If you display the specify and notes fields on these forms along side the checkboxes, there's going to be a lot of scrolling involved to check the boxes. I'd think that would be less user friendly than the combo boxes. Have your users thought about that?

 

[tbbrown32]

That's a good point. I know that for some of the forms, for example classifications, the main user changed their mind and wanted me to put one overall notes box instead of a specific one for each classification. However, that creates a problem given the way the tables are constructed, because she wants one note field to apply to all the classifications at the same time, which can't be done given the table structure. I think they'll probably have to be happy with no notes box or a lot of scrolling.

 

[tbbrown32]

Also one final question, as the user asked for a text box on the form. I've got the code that sneuberg created, but I just wanted to add one final field to it yet it won't compile correctly. I put a text box on the form and named it Text400 and I need it to write to the table only when the "Other" option is checked. Is there a good way to just insert that into the code below? I realize that this may not actually be possible, but was looking for some input. I thought about nesting another IF statement in there to look for a certain value of x and when it matched the "Other" column to write the text box contents to the table. Is that correct?

Private Sub Command283_Click()
Dim ws As DAO.Workspace
Dim x As Integer
If Me.NewRecord Then
Exit Sub
End If

Set ws = DBEngine(0)
DBEngine.Idle dbRefreshCache ' refresh read cache
ws.BeginTrans
For x = 1 To 26
If DCount("*", "tblIncidentClassifications", "InternalIncidentID = '" & Me.InternalIncidentID & "' AND ClassificationID = " & x) = 0 Then
If Me.Controls("Chk" & x) = True Then
CurrentDb.Execute "INSERT INTO tblIncidentClassifications(InternalIncidentID, ClassificationID) VALUES('" & Me.InternalIncidentID & "', " & x & ")", dbFailOnError
End If
Else
If Me.Controls("Chk" & x) = False Then
CurrentDb.Execute "DELETE FROM tblIncidentClassifications WHERE InternalIncidentID = '" & Me.InternalIncidentID & "' AND ClassificationID = " & x, dbFailOnError
End If
End If
Next x

ws.CommitTrans dbForceOSFlush ' flush the lazy-write cache
End Sub

 

[sneuberg]

To what field is the Text400 to be written? The Specify field? Is this "Other" checkbox an addition to the tblClassifications?

If you want to give the user just one text box per incident then why not put a field for it in the tblIncidents? You could call it Internal Incident Notes or Classification Note and put it on the tab. In that case you wouldn't need any additional code. It would be just like the other fields on the main form.

 

[tbbrown32]

That's a possibility, although I think that the user wanted four text boxes, one for each type of "Other" that exists in the table so I think I'll be stuck with additional code. I will check and then repost when I find out exactly what the user wants.

 

[sneuberg]

Then I'd add four fields to the tblIncidents table. If you want the Specify field to show only for the Other categories, I suspect that's going to involve some weird code.

On a difference issue when you get all of these checkboxes in place on the four tabs a considerable amount of code will be executed each time the current event fires (record change) and when the user clicks Save. This might get sluggish. Let me know if it's really bad. Maybe we can speed up the code.

 

[tbbrown32]

I added the four fields to tblIncidents table per your suggestion. It fits what the user wants and makes the most sense to avoid a lot of additional code. I will post back if it gets sluggish, but so far so good. Thanks again for all the help!