Solved

Runtime error 3346 - Multi select list box

Posted on 2008-09-30
9
1,223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 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