How to make checkboxes add records to junction table

[Wim]

What I have been using until today: a spreadsheet-like table containing
several dozens of fields, all Boolean (yes/no data type) + a form with
checkboxes linked to those fields (one checkbox + label for each field). Each
record represents the answers of one respondent to the questions of a survey.
What I have done: I created three tables: a respondent table and a questions
table, as well as a junction table with foreign keys relating back to
respondent number and question number.
What I want: maintain the exact same form, so that for my users nothing
seems to have changed, but, behind the scenes, each click on a checkbox adds
a record to the junction table. I do not want to use combo or list boxes, as
the number of options is too big for that. I am aware this probably involves
some programming.
Anybody out there can put me on the right track?

 

[Carl Rapson]

You will need to add code to the Click or AfterUpdate event of each checkbox
control, executing an INSERT statement to add the record to the table:

Private Sub chkBox1_Click()
If chkBox1.Value = True Then
DoCmd.RunSQL "INSERT INTO ..."
Else
' What do you do if the user un-checks it?
End If
End Sub

Of course, I don't know the names of your controls, so I'm using made-up
names here. This would have two drawbacks: it would result in a lot of
redundant code (basically the same code for each checkbox control), and what
do you do if the user un-checks a box after checking it?

If it's possible, it might be better for you to have a command button that
your users could click after checking all the appropriate boxes, and handle
the INSERTS there. You could loop through the checkbox controls and INSERT a
record for each checked one. Simpler coding, and it should work faster.
Something like:

Private Sub cmdButton_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctlValue = True Then
DoCmd.RunSQL "INSERT INTO..."
End If
End If
Next
End Sub

This assumes that there are no other checkboxes on your form except those
for answering questions. If there are, you could adding something to the Tag
property of each question checkbox and test for it first:

If ctl.ControlType = acCheckBox Then
If ctl.Tag = "Question" Then
If ctlValue = True Then
DoCmd.RunSQL "INSERT INTO..."
End If
End If
End If

Hopefully this will give you some ideas.

Carl Rapson

 

[Wim]

Carl, thank you very much for your help.
What do I want if the user unchecks a checkbox? Well, in that case the entry
in the junction table should be deleted.
I would like the form to operate as if the checkboxes were bound to
individual fields. That means that on opening the form on an existing record
(i.e. on a respondent whose answers have been introduced already) the form
should show checked checkboxes for every question that has an entry in the
junction table. Any change (from checked to unchecked or the other way
around) should cause the corresponding change (insertion or deletion) in the
table.
Can this be done by programming the AfterUpdate event of each of the
checkboxes? Do you think the amount of code would have a negative effect on
performance?
And if I use your second suggestion, doesn't that mean that the information
gets lost if the user forgets to click the button? My form has a lot of other
controls on it, apart from the group that corresponds to the survey
questions, so forgetting to push the button is something that could easily
happen.
I hope you are willing to give some more thought to the problem.

 

[Carl Rapson]

Yes, you could do that in the AfterUpdate event of each checkbox control. It
may or may not be slow, depending on the connection with your back-end
tables. As I said, it would be a lot of redundant code, but there's no
reason you couldn't do it that way. Alternatively, you could create a
procedure that does the insert and call that procedure in each AfterUpdate
event, passing the specific checkbox control as a parameter to the
procedure. That would reduce the redundancy.

And yes, if you went with my second suggestion the user would need to
remember to click the button. You could handle that by setting some sort of
flag internally whenever a checkbox is checked/unchecked, and test for that
flag when the form closes. In the click event of the button you would clear
that flag, so the form could close normally.

But there's no reason it wouldn't work your way, if that's your preference.
I'd say try it and see what the performance is like. If there's no noticable
delay when clicking each checkbox, then you're fine.

Carl Rapson

Carl, thank you very much for your help.
What do I want if the user unchecks a checkbox? Well, in that case the
entry
in the junction table should be deleted.
I would like the form to operate as if the checkboxes were bound to
individual fields. That means that on opening the form on an existing
record
(i.e. on a respondent whose answers have been introduced already) the form
should show checked checkboxes for every question that has an entry in the
junction table. Any change (from checked to unchecked or the other way
around) should cause the corresponding change (insertion or deletion) in
the
table.
Can this be done by programming the AfterUpdate event of each of the
checkboxes? Do you think the amount of code would have a negative effect
on
performance?
And if I use your second suggestion, doesn't that mean that the
information
gets lost if the user forgets to click the button? My form has a lot of
other
controls on it, apart from the group that corresponds to the survey
questions, so forgetting to push the button is something that could easily
happen.
I hope you are willing to give some more thought to the problem.

<snip>

 

[Wim]

Thanks, Carl, for your helpful comments. I guess I'll just have to experiment
with various options. Still I find it curious that there is no standard
solution for what seems to be a rather common problem (basically what I want
is a "multi-option option group").
Well, thanks again for your help.