Solved

How to import a multi select List Box into an Access table ?

Posted on 2006-11-07
4
442 Views
Last Modified: 2008-03-06
I created a ListBox named List481 which reads a table Y containing a field named Branch defined as an nvarchar type field.
Table Y, field Branch is the Row Source of the ListBox List481.
 
The ListBox is to allow the user to select multiple braanches. One branch selected per row.

Do you know how I could modify the following Sub Procedue to get a result set that I could then load into a table X with 1 field
also named Branch which would be populated with the Branches selected from List481 listbox.

Currently, the value returned as a result of selecting Branches 101 and 102 from the ListBox is as follows:

strInSt = "001"002"

I would eventually import the strInSt into an Access table X with field Branch.

Private Sub List481_AfterUpdate()

    Dim lst As Access.ListBox
    Dim vnt As Variant
    Dim strBranch As String
    Dim intColCount As Integer
    Dim strCycle As String
    Dim bolCycle As Boolean
    'Dim strInSt As String
    Dim strQ As String
     
    strQ = Chr$(34)
     
    strInSt = ""
                 
    Set lst = Me.List481
     
    For Each vnt In lst.ItemsSelected
       intColCount = lst.ColumnCount
       strBranch = lst.Column(0, vnt)
       strInSt = strInSt & strBranch & strQ
             
    Next vnt
End Sub


0
Comment
Question by:zimmer9
  • 3
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17894877


   For Each vnt In lst.ItemsSelected
       intColCount = lst.ColumnCount
       'strBranch = lst.Column(0, vnt)
       'strInSt = strInSt & strBranch & strQ
       Currentdb.execute "Insert into X (Branch) Values (" & .Itemdata(vnt) &")"      
    Next vnt
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17894882
if  field Branch is text

Currentdb.execute "Insert into X (Branch) Values ('" & .Itemdata(vnt) &"')"
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17894883
To keep the changes to a minimum change to

    ....
    For Each vnt In lst.ItemsSelected
       intColCount = lst.ColumnCount
       strBranch = lst.Column(0, vnt)
       strInSt = strInSt & "," & strQ & strBranch & strQ
    Next vnt
   
    strInSt = Mid(strInSt, 2)
    CurrentDb.Execute "INSERT INTO [table X] (Branch) SELECT Branch FROM [Table Y] WHERE Branch In (" & strInSt & ")"


Could be done with a recordset for better efficiency. :-)
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 17894892
forgat the lst

Currentdb.execute "Insert into X (Branch) Values (" & lst.Itemdata(vnt) &")"  
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

832 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