Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-11-07
4
Medium Priority
?
526 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 2000 total points
ID: 17894892
forgat the lst

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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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 …
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.

610 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