VBA add new records from list box to table - error

[steview]

Hi guys - not posted or done any development for a long time - so a little rusty

I have an issue when trying to add new records from a list box (contains several cells of data) into another table using the code below (part only)
If any of the data is "empty" a Data Type Conversion Error appears - if ALL cells have data works fine!

If Me.PriceListItemsPicked.ItemsSelected.Count = 0 Then
MsgBox "MUST SELECT @ LEAST 1 ITEM"
Exit Sub
End If
Set ctl = Me.PriceListItemsPicked
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!CustomersID = Me.CustomersID
rs!Customers = Me.CustomersID
rs!PriceListID = PriceListItemsPicked.Column(0, varItem)
rs!Category = PriceListItemsPicked.Column(1, varItem)
rs!Description = PriceListItemsPicked.Column(2, varItem)
rs!Supplier1PackQty = PriceListItemsPicked.Column(3, varItem)
rs!Qty = PriceListItemsPicked.Column(4, varItem)
rs!SupplierSale = PriceListItemsPicked.Column(5, varItem)
rs!DisplayOrder = PriceListItemsPicked.Column(6, varItem) + 99
rs.Update
Next varItem

 

[Gasman]

So make sure each record has all it's data populated.

Perhaps try using NZ() on the control values instead.

 

[arnelgp]

maybe add On Error Resume Next:

f Me.PriceListItemsPicked.ItemsSelected.Count = 0 Then
MsgBox "MUST SELECT @ LEAST 1 ITEM"
Exit Sub
End If
On Error Resume Next
Set ctl = Me.PriceListItemsPicked
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!CustomersID = Me.CustomersID
rs!Customers = Me.CustomersID
rs!PriceListID = PriceListItemsPicked.Column(0, varItem)
rs!Category = PriceListItemsPicked.Column(1, varItem)
rs!Description = PriceListItemsPicked.Column(2, varItem)
rs!Supplier1PackQty = PriceListItemsPicked.Column(3, varItem)
rs!Qty = PriceListItemsPicked.Column(4, varItem)
rs!SupplierSale = PriceListItemsPicked.Column(5, varItem)
rs!DisplayOrder = PriceListItemsPicked.Column(6, varItem) + 99
rs.Update
Next varItem
On Error Goto 0

 

[Ken Sheridan]

I have an issue when trying to add new records from a list box (contains several cells of data) into another table using the code below (part only)
If any of the data is "empty" a Data Type Conversion Error appears - if ALL cells have data works fine!

As you are inserting values into multiple columns over multiple rows, would it not be simpler to build and execute a single INSERT INTO statement?

The rows inserted would be derived from the same SELECT statement as the list box's RowSource property, and its WHERE clause would restrict the rows inserted by means of the IN operator against a value list of the key column's values. The following code snippet is an example of how the value list would be built:

        For Each varItem In ctrl.ItemsSelected
            strContactIDList = strContactIDList & "," & ctrl.ItemData(varItem)
        Next varItem
        
        ' remove leading comma
        strContactIDList = Mid(strContactIDList, 2)

 

[steview]

maybe add On Error Resume Next:

f Me.PriceListItemsPicked.ItemsSelected.Count = 0 Then
MsgBox "MUST SELECT @ LEAST 1 ITEM"
Exit Sub
End If
On Error Resume Next
Set ctl = Me.PriceListItemsPicked
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!CustomersID = Me.CustomersID
rs!Customers = Me.CustomersID
rs!PriceListID = PriceListItemsPicked.Column(0, varItem)
rs!Category = PriceListItemsPicked.Column(1, varItem)
rs!Description = PriceListItemsPicked.Column(2, varItem)
rs!Supplier1PackQty = PriceListItemsPicked.Column(3, varItem)
rs!Qty = PriceListItemsPicked.Column(4, varItem)
rs!SupplierSale = PriceListItemsPicked.Column(5, varItem)
rs!DisplayOrder = PriceListItemsPicked.Column(6, varItem) + 99
rs.Update
Next varItem
On Error Goto 0

Excellent - worked great - thank you

 

[steview]

As you are inserting values into multiple columns over multiple rows, would it not be simpler to build and execute a single INSERT INTO statement?

The rows inserted would be derived from the same SELECT statement as the list box's RowSource property, and its WHERE clause would restrict the rows inserted by means of the IN operator against a value list of the key column's values. The following code snippet is an example of how the value list would be built:

        For Each varItem In ctrl.ItemsSelected
            strContactIDList = strContactIDList & "," & ctrl.ItemData(varItem)
        Next varItem
       
        ' remove leading comma
        strContactIDList = Mid(strContactIDList, 2)

Thanks Ken - will try but previous reply worked ok - Thank you