• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1246
  • Last Modified:

Runtime error 3346 - Multi select list box

I'm getting a runtime 3346 error on the following code.  Any help at debugging would be greatly appreciated.   I have a multi select list box on a form.  I'm trying to append the contents of the selection (6 columns) to my table tblOOSInventory.  

Dim strItems As String
    Dim intItem As Integer
    For intItem = 0 To List2.ListCount - 1
        If List2.Selected(intItem) Then
            strItems = strItems & List2.Column(0, intItem) & ";" & _
                                  List2.Column(1, intItem) & ";" & _
                                  List2.Column(2, intItem) & ";" & _
                                  List2.Column(3, intItem) & ";" & _
                                  List2.Column(4, intItem) & ";" & _
                                  List2.Column(5, intItem) & ";"
        End If
       
 >>>>>> Error 3346 happens here >>>>> Currentdb.Execute "Insert into tblOOSInventory(InvIssID, EmpID, IssBC, InvType, Size, ILOC) values (stritems)"
    Next intItem
0
AnneYourPointIs
Asked:
AnneYourPointIs
  • 5
  • 4
1 Solution
 
Rey Obrero (Capricorn1)Commented:
first post the data type of the following fields

InvIssID, EmpID, IssBC, InvType, Size, ILOC


0
 
AnneYourPointIsAuthor Commented:
Hello cap,

Thanks for responding.  The following are the field datatypes:

InvIssID = Number
EmpID = Number
IssBC=Text
InvType=Text
Size= Text
ILoc=Text
0
 
Rey Obrero (Capricorn1)Commented:

Dim strItems As String
    Dim intItem , sql

    With Me.List2
    For intItem = 0 To .ListCount - 1
        If .Selected(intItem) Then
            strItems = strItems & .Column(0, intItem) & "," & _
                                  .Column(1, intItem) & "," & _
                                Chr(39) & .Column(2, intItem) & Chr(39) & "," & _
                                Chr(39) & .Column(3, intItem) & Chr(39) & "," & _
                                Chr(39) & .Column(4, intItem) & Chr(39) & "," & _
                                Chr(39) & .Column(5, intItem) & Chr(39)

        sql = "Insert into tblOOSInventory(InvIssID, EmpID, IssBC, InvType, Size, ILOC) values (" & strItems & ")"
        currentdb.execute sql
        End If
    Next
    End With
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
AnneYourPointIsAuthor Commented:
I'm now getting a runtime error 3075 syntax error missing operator in query expression "P'597"

Dim strItems As String
    Dim intItem, sql

    With Me.List2
    For intItem = 0 To .ListCount - 1
        If .Selected(intItem) Then
            strItems = strItems & .Column(0, intItem) & "," & _
                                  .Column(1, intItem) & "," & _
                                Chr(39) & .Column(2, intItem) & Chr(39) & "," & _
                                Chr(39) & .Column(3, intItem) & Chr(39) & "," & _
                                Chr(39) & .Column(4, intItem) & Chr(39) & "," & _
                                Chr(39) & .Column(5, intItem) & Chr(39)

        sql = "Insert into tblOOSInventory(InvIssID, EmpID, IssBC, InvType, Size, ILOC) values (" & strItems & ")"
>>>> 3075 occurs here>>>>        Currentdb.Execute sql
        End If
    Next
    End With
0
 
Rey Obrero (Capricorn1)Commented:
what column is this coming from { "P'597" }
0
 
AnneYourPointIsAuthor Commented:
Im not sure what the "P" is but 597 is column 0, InvIssID
0
 
Rey Obrero (Capricorn1)Commented:
forget to clear strItems


Dim strItems As String
    Dim intItem, sql

    With Me.List2
    For intItem = 0 To .ListCount - 1
        If .Selected(intItem) Then
            strItems = strItems & .Column(0, intItem) & "," & _
                                  .Column(1, intItem) & "," & _
                                Chr(39) & .Column(2, intItem) & Chr(39) & "," & _
                                Chr(39) & .Column(3, intItem) & Chr(39) & "," & _
                                Chr(39) & .Column(4, intItem) & Chr(39) & "," & _
                                Chr(39) & .Column(5, intItem) & Chr(39)

        sql = "Insert into tblOOSInventory(InvIssID, EmpID, IssBC, InvType, Size, ILOC) values (" & strItems & ")"
 Currentdb.Execute sql
       
     strItems=""            '<<<ADD this line

        End If
    Next
    End With
0
 
AnneYourPointIsAuthor Commented:
great!  Thanks so much for your help.
0
 
AnneYourPointIsAuthor Commented:
Thanks Cap!  You always come through for me.  I appreciate it a lot.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now