Solved

Runtime error 3346 - Multi select list box

Posted on 2008-09-30
9
1,206 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 500 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

786 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