Results 1 to 6 of 6
  1. #1
    AAAndy is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    68

    How to populate list box from an array?

    How can I populate list box from an array?



    I have an array
    Dim LArray() As String

    I only have values in LArray(0) LArray(1) LArray(2) LArray(3) LArray(4) , each one of those has several different values

    how do I populate all the values to a listbox from LArray(1) only
    LArray(1) has emails that are separated by an ;

    I have tried several ways but they all fail.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not sure I understand
    each one of those has several different values
    Perhaps you could show us the values of your array, and tell us what you intend to do with the Listbox.

  3. #3
    AAAndy is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    68
    Quote Originally Posted by orange View Post
    Not sure I understand

    Perhaps you could show us the values of your array, and tell us what you intend to do with the Listbox.
    LArray(1) values are all emails separated by ; , for example below

    aaaa@aaa.com;bbbb@bbbbbb.com;cccc@cccc.com

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I created a form called Form19. It has only a listbox List0.
    Then wrote a small vba code to populate a value list in the List0 rowsource
    Code:
    Sub aray()
        Dim Larray(1) As String
        Larray(1) = "aaaa@aaa.com;bbbb@bbbbbb.com;cccc@cccc.com"
        Forms!Form19.List0.RowSource = Larray(1)
    'I stepped through this code and watched the rowsource get populated in the design view of the form.
    End Sub
    Forms!Form19.List0.RowSource = Larray(1) is the line that populates the rowsource.

    see attached jpgs.

    Click image for larger version. 

Name:	Form19Design.jpg 
Views:	13 
Size:	55.3 KB 
ID:	25508

    Click image for larger version. 

Name:	Form19ListBoxWithData.jpg 
Views:	13 
Size:	8.6 KB 
ID:	25509

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    so you have something like


    Array element 0: aaa@aaa.com;bbb@bbb.com;ccc@ccc.com
    Array element 1: ddd@ddd.com;eee@eee.com

    and so on

    what you want, then I would assume, is something in your list like:

    aaa@aaa.com
    bbb@bbb.com
    ccc@ccc.com
    ddd@ddd.com
    eee@eee.com
    etc.

    To do that you'd have to cycle through your array elements, then cycle through the sub elements of each array
    (using this method because I'm not sure if you can use orange's method to concantenate values in the list)

    something like:
    Code:
    Dim LArray As Variant
    Dim LSubArray As Variant
    
    
    LArray = Array("aaa@aaa.com;bbb@bbb.com;ccc@ccc.com", "ddd@ddd.com;eee@eee.com", "fff@fff.com", "ggg@ggg.com;hhh@hhh.com;iii@iii.com;jjj@jjj.com")
    For i = 0 To UBound(LArray)
        LSubArray = Split(LArray(i), ";")
        For j = 0 To UBound(LSubArray)
            Me.List0.AddItem LSubArray(j)
        Next j
    Next i
    I just did a variant of orange's code and it's sleeker you just have to be careful with the syntax:

    Code:
    Dim LArray As Variant
    
    
    LArray = Array("aaa@aaa.com;bbb@bbb.com;ccc@ccc.com", "ddd@ddd.com;eee@eee.com", "fff@fff.com", "ggg@ggg.com;hhh@hhh.com;iii@iii.com;jjj@jjj.com")
    Me.List0.RowSource = ""
    
    
    For i = 0 To UBound(LArray)
        Me.List0.RowSource = Me.List0.RowSource & IIf(i = 0, "", ";") & LArray(i)
    Next i

  6. #6
    AAAndy is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    68
    It works now, Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Selection from Combo Box to populate list
    By Cupragsw in forum Forms
    Replies: 3
    Last Post: 04-13-2015, 10:49 AM
  2. Populate Combo Box with list of Tables
    By Varda in forum Forms
    Replies: 4
    Last Post: 06-07-2013, 08:07 AM
  3. Replies: 1
    Last Post: 11-23-2012, 10:26 PM
  4. list files recursively and store results in array
    By maxbre in forum Programming
    Replies: 2
    Last Post: 11-10-2011, 01:49 AM
  5. List box to populate other list boxes
    By Nathan in forum Forms
    Replies: 0
    Last Post: 03-03-2009, 07:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums