Solved

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

Posted on 2006-11-07
4
427 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
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…

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now