Code for using Checkbox value to write to associative table (1 Viewer)

[tbbrown32]

A little relevant background: I have 3 tables (tblncident, tblClassification, and an associative tblIncidentClass). I currently have a form with tabs, and on each tab I have a subform linked under master fields with a field titled "InternalIncidentID" so that as I go through InternalIncidentIDs the subforms on the tabs change accordingly.

One of my tabs with a subform currently has a combo box where the user can select ClassificationName (a field in tblClassification), and unseen in the background the InternalIncidentId (PK of tblIncident) and the ClassificationID (PK of tblClassification) are written to the associative table (and are the table's composite PKs).

Instead of this approach (which does work), the user wants me to remove the combo boxes and use check boxes to present all 20-25 ClassificationName options instead. This is a bit of a challenge for me, as I am still somewhat of a beginner in Access. I need help with the programming, as I need to somehow use the checkbox Yes/No value and assign the appropriate ClassificationID and InternalIncidentID to the table in the background. The user also needs to be able to check as many of the boxes as possible. I would really, really appreciate some help and guidance on the code since I am a relative newbie when it comes to VBA. Thanks in advance!

 

[pbaldy]

It sounds like you want to populate a normalized table from an un-normalized form. Doable, but a little more work. If a multiselect listbox would work:

http://www.baldyweb.com/MultiselectAppend.htm

The problem with using checkboxes is that when they add/drop classifications, you need to modify the form. A listbox would get the options from the table, so no design modifications would be necessary when classifications changed.

 

[sneuberg]

One of my tabs with a subform currently has a combo box ....

. The user also needs to be able to check as many of the boxes as possible.

I'm confused. It sounded like the user had a combo box from which he made one choice (which worked) but now needs to make multiple choices. If you just want to turn a combo box into a collection of Yes/Nos that represented the choices the combo box offered I'd say use an option group. The option group can only have numbers as values so you would have to set up a table to translate the number to the values that were in the combo box. This would work if only one value is selected at a time.

If this is not the case using checkboxes is going to get messy and I'd find out first if your user would be willing to use the multiselect listbox suggested by pbaldy. If your user insists on the checkboxes let us know and I'll try to set up something for you that might minimize the mess.

 

[tbbrown32]

My apologies, I should have been a bit more specific than I was as far as the combo box goes... With the combo box on the form (and the default form view set to Continuous) the user can select one option at a time from the combo box but ultimately can select as many unique choices as they want to (i.e. 20-25 total options, they can select up to that many with one choice in each combo box). Unfortunately, the user is pretty insistent on the check boxes, but that will pose a problem. Some code that has been suggested is below. My trouble is I keep getting the Next without For error. I know I'm missing something simple here, but being relatively new to VBA I can't find what it is.

Dim x As Integer
For x = 1 to 20
If Me.Controls("Chk" & x) = True Then
CurrentDb.Execute "INSERT INTO tblIncidentClass(InternalIncidentID, ClassificationID) VALUES(" & Me.IncidentID & ", " & x & ")"
Next
Me.Requery

 

[pbaldy]

You're missing

End If

 

[tbbrown32]

Thanks, I missed the end if... I have code that compiles, but doesn't actually Insert Into the table that I specify in the code. Is there some other potential issue with the code below?

Private Sub Command283_Click()
Dim x As Integer
For x = 1 To 2
If Me.Controls("Chk" & x) = True Then
CurrentDb.Execute "INSERT INTO tblIncidentClassifications(InternalIncidentID, ClassificationID) VALUES(' & Me.InternalIncidentID & ', ' & x & ')"
End If
Next x
Me.Requery
End Sub

 

[pbaldy]

Yes, the single quotes on the inside should be doubles...like they were before.

 

[sneuberg]

CurrentDb.Execute "INSERT INTO tblIncidentClassifications(InternalIncidentID, ClassificationID) VALUES(" & Me.InternalIncidentID & "," & x & ")"

if both are numbers in the database

 

[tbbrown32]

After changing the quotes to double, I'm getting runtime "error 3061. Too few parameters. Expected 1" like I was before. Is there a further adjustment I should make with the code?

 

[pbaldy]

Use a string for the SQL and this method to see how it's coming out:

http://www.baldyweb.com/ImmediateWindow.htm

From the error, I suspect something is spelled wrong.

 

[sneuberg]

This is probably getting ahead of the current situation but you may not be able to insert in the tblIncidentClassifications table without first inserting into the tblClassification and the tblIncident tables first. You might want check to see if your relationships allow you to do this. But I don't think this would give you the error you are currently getting.

 

[pbaldy]

Along the same lines, you can add to the Execute line so it will error rather than fail silently if it fails to insert. Presuming you switch to a variable:

CurrentDb.Execute VariableName, dbFailOnError

Based on the error I don't think that will help here, but it could down the line.

 

[tbbrown32]

Hmm... The command runs until hit reaches the Insert Into statement, as Debug.Print x reveals the value of x up until it reaches the Insert Into, then any Debug.Print x further along in the programming reveals no value. Is it possible since it is a composite primary key and can't write one value to the table without a value for the other one, I need to disable that in the code to allow both fields to be inserted at the same time?

 

[pbaldy]

Can you attach the db here?

 

[tbbrown32]

I've attached the db.. most of it is irrelevant, the relevant part is the first tab with the unbound checkboxes and button that runs the code. Thanks!

 

[pbaldy]

The first field is text so requires delimiters:

CurrentDb.Execute "INSERT INTO tblIncidentClassifications(InternalIncidentID, ClassificationID) VALUES('" & Me.InternalIncidentID & "', " & x & ")"

 

[sneuberg]

I don't see Me.InternalIncidentID on the form. Disregard looking at the wrong form

 

[sneuberg]

This is probably getting ahead of the current situation but you may not be able to insert in the tblIncidentClassifications table without first inserting into the tblClassification and the tblIncident tables first. You might want check to see if your relationships allow you to do this. But I don't think this would give you the error you are currently getting.

I see now that this is not a concern, at least as long a x is less than 23.

 

[tbbrown32]

Thanks for taking a look at it! I added in the delimiters for the text field, but I am still getting the same "error 3061. Too few parameters. Expected 1".

 

[pbaldy]

That line is working for me in the database you attached.