?
Solved

Runtime error 3346 - Multi select list box

Posted on 2008-09-30
9
Medium Priority
?
1,240 Views
Last Modified: 2010-04-21
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
Comment
Question by:AnneYourPointIs
  • 5
  • 4
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22604700
first post the data type of the following fields

InvIssID, EmpID, IssBC, InvType, Size, ILOC


0
 

Author Comment

by:AnneYourPointIs
ID: 22604734
Hello cap,

Thanks for responding.  The following are the field datatypes:

InvIssID = Number
EmpID = Number
IssBC=Text
InvType=Text
Size= Text
ILoc=Text
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22604815

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:AnneYourPointIs
ID: 22604892
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22604952
what column is this coming from { "P'597" }
0
 

Author Comment

by:AnneYourPointIs
ID: 22604981
Im not sure what the "P" is but 597 is column 0, InvIssID
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 22605009
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
 

Author Comment

by:AnneYourPointIs
ID: 22608536
great!  Thanks so much for your help.
0
 

Author Closing Comment

by:AnneYourPointIs
ID: 31501700
Thanks Cap!  You always come through for me.  I appreciate it a lot.  
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question