Listbox Issue

[Voodeux2014]

I am trying to add information into a table based on all selected items in a listbox.

tbl2Campaign_Trainers is the table
has three fields.
field1 = CampaignID
field2 = TrainerID
field3 = Active (yes/no field, this is used in a query subform later)

frmAssignCampaign is the form.
has a combobox and a listbox.


combobox - (lets you select the Trainer and bound to TrainerID)
listbox - (lets you select which campaigns are assigned to the trainer and bound to CampaignID)

Private Sub Command20_Click()
    Dim conceptValue As String
    Dim strInsert As String
    Dim i As Variant


    For Each i In Me.lstCampaigns.ItemsSelected
        conceptValue = conceptValue & Me.lstCampaigns.ItemData(i)
    Next i


    strInsert = "INSERT INTO tbl2Campaign_Trainers(CampaignID) " & "VALUES('" & conceptValue & "');"
    DoCmd.RunSQL strInsert
    
    DoCmd.Close acForm, "frmAssignCampaign", acSaveNo
End Sub

The code is listed above. All that the code successfully does right now is adds the TrainerID to the table but keeps the CampaignID blank.
Having the information saved in multiple records is fine, actually preferred, as that will keep the option to only display active campaigns in the query subform later.
What in my code needs to be changed to make this successful?

[June7]

If table and controls are bound, why are you using code to create record? Bound means the ControlSource property is set to a field of form RecordSource. Selected values in bound combo and list boxes are passed directly to table. If you want to use a multi-select listbox and code to create records, do not use bound controls.

And the SQL action will have to populate both TrainerID and CampaignID fields. Do you want the Active field set to Yes? Is the field DefaultValue property set for Yes?

[Voodeux2014]

I attempted your approach. I removed the code and just used the controls as is.
When clicking the btnSave, it only adds the TrainerID to the table and does not list the CampaignID that were selected in the listbox. The way that they table is currently structured is that it would require a new record for each TrainerID/CampaignID combination.

[June7]

If you want to automate creating multiple records, must use code. My point was cannot also have the controls bound to fields.

If TrainerID and CampaignID are number type fields, do not use apostrophe delimiters.

"INSERT INTO tbl2Campaign_Trainers(TrainerID, CampaignID) VALUES('" & Me.TrainerID & "', '" & conceptValue & "');"

[Voodeux2014]

Finally got it to work and here is the code:

Private Sub Command20_Click()    Dim i As Variant
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qd As DAO.QueryDef
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tbl2Campaign_Trainers", dbOpenTable)
    
    For Each i In lstCampaign.ItemsSelected
        rst.AddNew
        rst!CampaignID = lstCampaign.ItemData(i)
        rst!TrainerID = Me.cboTrainer
        rst.Update
    Next i
    Set rst = Nothing
    Set qd = Nothing
    DoCmd.Close acForm, "frmAssignCampaign", acSaveNo
End Sub

[June7]

That's one way, however, your original code method should have worked as well.

Glad you got a solution.