Using Multiple Checkboxes in a Normalized Database to add new records

[BabyLucifer666]

Hello All, (using Access2000)

I have a form with multiple unbound checkboxes. What I would like to do is have the user check whoever needs to take a specific training course. My database is normalized, I have no Yes/no fields in my tables, what I have is VB code that inputs an IDnumber into the field that matches the specific job title for the employee that needs to take that course:

Private Sub chkMFGAssoc_Click()
 If Me.chkMFGAssoc = -1 Then
 Me.GroupID.Value = "0015"
 Else
 Me.GroupID.Value = ""
 End If
End Sub

This works great, it adds a new record in the join table (called "tblClassGroup") with the classID and the GroupID. My problem is that most of the time one class is trained to multiple Groups. I need to be able to click on multiple checkboxes and create multiple records using a single form and vb code (if possible!)

Some info:
tblGroups has GroupID feild GroupName Field
tblClass has ClassID, ClassName, Description, Trainer
Join - tblClassGroup has ClassGroupID, ClassID, GroupID

Thank you
V

[beacon]

Hi V,

Have you looked into using the INSERT INTO SQL statement in association with the DoCmd.RunSQL command? This will create a new row in your table and will insert the values that you indicate into the appropriate fields.

[thelonelyghost]

Like what @beacon was saying, you could run an INSERT INTO statement to create a new record. I'm not exactly sure how your tables are formed, but one use of this code is made safer by making a submit button, then coding it like so:

Private Sub Submit_Click()
Dim strSQL As String
 
If Me.chkMFGAssoc = -1 Then
  strSQL = "INSERT INTO [tblMFGAssocClass] ([GroupID], [FieldName2], [FieldName3]) VALUES (" & Me.GroupID.Value & ", " & Me.ControlName2.Value & ", " & Me.ControlName3.Value & ");"
 
  DoCmd.RunSQL strSQL
End If
If Me.chkOtherCheckbox = -1 Then
  strSQL = ...
  DoCmd.RunSQL strSQL
End If
 
 ...
 

You'll have to add a ' before and after each control value if it's a string, or a # if it's a date, but other than that it should work. Just repeat the If-Then statements for each checkbox while adapting the SQL code for each rule and you should be fine.

Alternately you can use CurrentDb.Execute instead of DoCmd.RunSQL (see THIS LINK) or (if need be) execute an UPDATE query instead of INSERT INTO, but that's up to you to figure out its uses. (n_n)

[BabyLucifer666]

Thank you both for you help. I am attempting to enter the code now, but got lost at:

(" & Me.GroupID.Value & ", " & Me.ControlName2.Value & ", " & Me.ControlName3.Value & ");"

The table is being updates is called

tblClassGroup
ClassGroupID (primarykey)
ClassID
GroupID

the checkmarks need to represent the groupID field.

Thanks again

[Steven Kogan]

If ClassGroupID is an autonumber field, you do not need to specify it in the SQL. If ClassID is stored in the ClassID field, line 5 might be:

strSQL = "INSERT INTO [tblClassGroup] ([GroupID], [ClassID]) VALUES (15, " & Me.ClassID.Value & ");"

[BabyLucifer666]

I think i got it, but when i attempted to test, i got an syntax error

Private Sub btnEnter_Click()
Dim strSQL As String
 
If Me.chkHRRep = -1 Then
    strSQL = "INSERT INTO tblClassGroup ([ClassGroupID], [ClassAreaStationID], [GroupID]) VALUES (" & Me.ClassGroupID.Value & ", " & Me.txtClassAreaStationID.Value & ", " & 19 & ")"
 
    DoCmd.RunSQL strSQL
End If
 
End Sub

the error is in the DoCmd.RunSQL strSQL line

[Steven Kogan]

What is the error message displayed?

If ClassGroupID is an autonumber field then you should not be able to specify it in you INSERT INTO sql.

[beacon]

Is Me.txtClassAreaStationID.Value a text field? If so, you need to add single quotes on the inside of the double quotes surrounding the field in the SQL string.

It will look like this:

strSQL = "INSERT INTO tblClassGroup ([ClassGroupID], [ClassAreaStationID], [GroupID]) " & _
         "VALUES (" & Me.ClassGroupID.Value & ", '" & Me.txtClassAreaStationID.Value & "', " & 19 & ")"
 

Notice the single quotes: VALUES (" & Me.ClassGroupID.Value & ", '" & Me.txtClassAreaStationID.Value & "', " & 19 & ")"

[BabyLucifer666]

Trying it without the primary key field noted

Private Sub btnEnter_Click()
Dim strSQL As String
 
strSQL = "INSERT INTO tblClassGroup (ClassAreaStationID, GroupID) " & _
         "VALUES ('" & Me.txtClassAreaStationID.Value & "', " & 19 & ")"
 
    DoCmd.RunSQL strSQL
 
End Sub

I get an error saying it can't append do to key violations.

trying it with the primary key as follows:

Private Sub btnEnter_Click()
Dim strSQL As String
 
strSQL = "INSERT INTO tblClassGroup (ClassGroupID, ClassAreaStationID, GroupID) " & _
         "VALUES (" & ClassGroupID.Value & ", '" & Me.txtClassAreaStationID.Value & "', " & 19 & ")"
 
    DoCmd.RunSQL strSQL
 
End Sub

Also get the same error that I can not append due to key violations

Thank you guys so much for your help!!

[thelonelyghost]

Three things:

1. Check the datatype of ClassGroupID
2. What are the fields, tables, and datatypes?
3. Syntax issues

(1)
To start, I apologize because I misunderstood the situation with your tables. I thought ClassID, GroupID, and ClassGroupID were all different tables. Since you said that ClassGroupID is the primary key, are you positive it's an autonumber datatype? If not, that may be your issue. If it is, @Steven Kogan is right: you shouldn't need to even address that field in strSQL.

(2)
Additionally, I'm confused as to what the fields are in your table. I see that you said they are ClassID, GroupID, and ClassGroupID (Primary Key) in post 4 but then you bring up fields such as ClassAreaStationID... are there more fields affected your forgot to tell us about?

(3)
One thing I see wrong in the following code (syntactically speaking) is the use of "', " & 19 & ")". If it's a constant value then there's no need to concatenate like that. The use of & in this sort of case is mostly so that the code can be dynamic to the form's values. Just write it as "', 19)" and that may solve your issue. (see post 11)

strSQL = "INSERT INTO tblClassGroup (ClassAreaStationID, GroupID) " & _
"VALUES ('" & Me.txtClassAreaStationID.Value & "', " & 19 & ")"

strSQL = "INSERT INTO tblClassGroup (ClassGroupID, ClassAreaStationID, GroupID) " & _
"VALUES (" & ClassGroupID.Value & ", '" & Me.txtClassAreaStationID.Value & "', " & 19 & ")"

[thelonelyghost]

Also, I figured I should clarify what "(" & Me.ControlName.Value & ", " is supposed to mean. As you know, you can define a string like so: strString1 = "Hello World!". To make the value dynamic, you can create a form that has you input a value in the string, i.e. strString2 = "Hello " & Me.txtName.Value & "!". The title of the form's input (control) in this case is "txtName".

Let's say I input the name "Tom" into the form. If you printed the value of strString1 and strString2 after they've been set, you would see

strString1:
"Hello World!"
 
strString2:
"Hello Tom!"

Did that help clear things up in post 4?

[BabyLucifer666]

I finally got this working. I was getting a key violation and after doing some research, found that if i took off the "referential integrity" it would work.. and it did.

(NOte: I made a bunch of changes to my tbl structure for another reason, so the names are different)

Thank you so much for you help!!

Private Sub btnEnter_Click()
    Dim strSQL As String
 
If Me.chkHRAdmin = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0008')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkHRRep = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0002')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkMatBuy = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0004')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkMatIA = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0010')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkMatLead = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0015')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkMatShip = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0021'')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkPDCAD = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0005')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkPDENG = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0026')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkPDLead = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0017')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkPDTech = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0007')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkMFGAssoc = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0011')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkMFGTech = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0014')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkMFGENG = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0012')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkMFGMgr = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0013')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkQAAdmin = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0001')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkQALead = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0020')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkQAPI = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0018')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkQAIA = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0009')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkOPSIT = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0022')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkPlantMgr = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0016')"
 
    DoCmd.RunSQL strSQL
End If
 
If Me.chkOPSSup = -1 Then
    strSQL = "INSERT INTO tblClassAreaStation (CategoryID, ClassID, AreaID, StationID, JobTitleID)" & _
    "VALUES ('" & Me.cboCategory.Value & "', '" & Me.txtClassAreaStationClassID.Value & "', '" & Me.cboArea.Value & "', '" & Me.cboStation.Value & "', '0025')"
 
    DoCmd.RunSQL strSQL
End If
 
End Sub

My last question for this would just be how do i turn off the warnings so the user doesn't get "you are about to append..." message for every record that is being appended?

~BL

[NeoPa]

One way is to use SetWarnings.

Call DoCmd.SetWarnings(False)
Call DoCmd.RunSQL(strSQL)
Call DoCmd.SetWarnings(True)

The other is to use :

Call CurrentDB.Execute(strSQL)

The Execute method doesn't display the messages. Use the dbFailOnError option to handle items that are locked or otherwise unable to be effected by the query.