Solved

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

Posted on 2006-11-07
4
488 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
[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
  • 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

705 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