Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

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

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

581 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