Insert Rows from MultiSelect Listbox Into a Table

[koolwaters]

Hi,

I have a bound subform which contains a multiselect listbox.

I am trying to insert the values from this multiselect listbox along with one other value into a table.

The values I am trying to insert are:
the foreign key field
the value/values selected in the listbox

I am using the code below in the Before Update property of the subform but if more than one item is selected in the listbox only one is inserted into the table and I get an error "Index or primary key cannot contain a Null value".

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varItem As Variant
Dim strSQL As String

    For Each varItem In Me.SOID.ItemsSelected

        strSQL = "INSERT INTO tblMatSO (MatID, SOID) " _
                & "Values(" & Me.MatID & ", " _
                                    & Me.SOID.Column(0, varItem) & ")"
    Next

    DoCmd.RunSQL strSQL
    
End Sub

Any suggestions?

Thanks

 

[xenou]

Debug.Print your sql so you can view it. FInd out what Nulls you are trying to insert. On the face of it, it seems some of the values in the listbox must be null and you are trying to insert them. Or else Me.MatID is null and that's probably bad too.

        strSQL = "INSERT INTO tblMatSO (MatID, SOID) " _
                & "Values(" & Me.MatID & ", " _
                                    & Me.SOID.Column(0, varItem) & ")"
        
        debug.print strSQL
        
        'or: MsgBox strSQL

 

[koolwaters]

Here is what I get when debugging.

INSERT INTO tblMatSO (MatID, SOID) Values(1, 3)

Even though I have two items selected in the list box, the SQL only inserts the record for the second selected item.

 

[koolwaters]

The listbox is on a subform. This subform is linked to a table which has two fields, the Master Field (MatID) and the Listbox Value (SOID).

When the record is created on the Main Form, I need to link that record to one or many items from the listbox.

There are a maximum of five items that can be selected in the listbox so if I have all five selected, I would want the below entered in the table

1,1
1,2
1,3
1,4
1,5

 

[xenou]

I don't think you are using the correct code to loop through the items.

 

[xenou]

THis example came from one site:

Dim EachDepartment as Long
For EachDepartment = 1 To Me.lstDepartment.ListCount
    If Me.lstDepartment.Selected(EachDepartment - 1) = True Then
        Debug.Print Me.lstDepartment.List(EachDepartment - 1)
    End If
Next EachDepartment

Some more examples here (building sql where criteria instead of a insert sql) -- seems like code is slightly different depending on whether you are using the bound column or another column (you might be using "another" column in fact).
My Access Tips