Results 1 to 15 of 15
  1. #1
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62

    How to create Dynamic Array

    Hello everyone,



    I have a multi-select combo box whereby I need to create an array from information that was clicked and create a sentence, in order, from the way the array was input.

    Here's what I had after doing a bunch of testing last week:

    Code:
    Private Sub Command3_Click()
     
    Dim text2 As String
    Dim output2 As String
    Dim item2 As Variant
     
    'Fill a string with data which is semi-colon delimited; test by uncommenting one at a time.
    'text2 = "Allison"
    'text2 = "Allison;Brent"
    'text2 = "Allison;Brent;Carl"
    'text2 = "Allison;Brent;Carl;Darryl"
    text2 = "Allison;Brent;Carl;Dan;Ed;Fred;Gail;Hank;Ichabad;Jack;Kermit"
     
    'Split the text and put it into a dynamic array
    names2() = Split(text2, ";")
    
    output2 = Join(names2, ",")
      
    'VBA for appropriate commas and 'and'
    Dim result As String
    For i = 0 To UBound(names2())
        If UBound(names2()) = 0 Then
            result = names2(0) & " is here alone."
        ElseIf UBound(names2()) = 1 Then
            result = names2(0) & " and " & names2(1) & " are here together."
        Else
            If i = UBound(names2()) Then
                result = result & names2(i) & " were all here."
            ElseIf i = (UBound(names2()) - 1) Then
                result = result & names2(i) & ", and "
            Else
                result = result & names2(i) & ", "
           End If
        End If
    Next
    MsgBox result
     
    End Sub
    I understand how it's looping through the pre-provided string (text2), but I need the ability to actually create that string, myself. Example, if I have the below combo box with the options selected, I want to be able to spit the information back out as: ..."went to the park, watched TV, took a shower, went to bed"...
    Click image for larger version. 

Name:	Verbs.PNG 
Views:	30 
Size:	8.6 KB 
ID:	33192

    I know how, from the VBA, above, to hand-jam that text string, change the delimiter, and get the desired output, but what I don't know is how to identify the boxes that are checked and to insert those with a -1 value into that dynamic array. I'm certainly not looking for anyone to do my work for me, but if anyone has a good link on how to create arrays based on the values entered in a multi-selectable combo box, I would certainly be thankful!

    Thank you!
    Last edited by Ramtrap; 03-20-2018 at 09:10 AM. Reason: removed extraneous information

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this is a database, nobody uses arrays.
    but if you want to cycle thru the list:

    here the listbox is bound to the word, and col 2 is the true/false result.
    in vb, columns begin at zero, so column(1) is really column 2 to humans.

    Code:
    dim i as integer
    dim itm, vLine
    
    for i = 0 to lstBox.listcount -1
        itm = lstBox.itemdata(i)    'get next item in listbox
        lstBox = itm         'set the listbox to this item
        if lstBox.column(1) = true then
             vLine = vLIne & "," & Itm   
        endif
    next
    
    msgbox vLIne
    end sub

  3. #3
    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

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I use arrays on a frequent basis.
    There are many occasions where they are useful or even essential in a database
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I use array in VBA. Some advocate Collection object instead but I learned with arrays.

    Note that ranman's code is cycling through a listbox, not a multi-select combobox.

    AFAIK, multi-select combobox must be bound to a multi-value field and code cannot address the list. A multi-value field does not contain a string value therefore it cannot be constructed nor parsed with string manipulation functions. Review https://support.office.com/en-us/art...C-6DE9BEBBEC31
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by June7 View Post
    I use array in VBA. Some advocate Collection object instead but I learned with arrays.

    Note that ranman's code is cycling through a listbox, not a multi-select combobox.

    AFAIK, multi-select combobox must be bound to a multi-value field and code cannot address the list. A multi-value field does not contain a string value therefore it cannot be constructed nor parsed with string manipulation functions. Review https://support.office.com/en-us/art...C-6DE9BEBBEC31
    I will have to study a bunch to do this, but in your collective experience, would it make more sense to maybe look through accessing the data using DAO? All that data is going to be stored in the table, so I should be able to access that field, right? I would assume it's treated as a string (comma delimited), or am I too hopeful?

    The bottom line goal is I want to be able to have something like the below table / form and separate all the user-selections so that I can ultimately put (as examples; note I am following the order the options show up and keeping that same order, and looking to mix it in with the code I'd placed at the top):
    Glenda went to the park.
    Harold went to the store and met Michael for lunch.
    Joe ran over to the bank, had dinner, and went to bed.
    Sue went to the store, took a nap, watched TV, took a shower, and went to bed.
    Click image for larger version. 

Name:	TableView.png 
Views:	20 
Size:	23.4 KB 
ID:	33204

    So can anyone let me know if this is possible by accessing the table (or query? or form?) and taking the data out of that field, separating it, and applying my Sub Command3_Click(),per above?

    Thank you!

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    As already stated, multi-value field does not store a string. It may look like a CSV but it is not. I provided a link to MS support that explains multi-value fields. What you want to do won't be easy.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by June7 View Post
    As already stated, multi-value field does not store a string. It may look like a CSV but it is not. I provided a link to MS support that explains multi-value fields. What you want to do won't be easy.
    I apologize, you're right. I will look again at the link and see if I can figure it out and worse comes to worse, I should be able to change the way I want my form to look to eventually concatenate textboxes to make an array. That might be the easiest thing to do, though it won't look as cool, lol. Thank you!

  9. #9
    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'm not sure where you want to use this set up, but if you are making a list of activities such that a person could select from the list and store the info, you might consider:

    a list of verbs (went, took, ate, ran,stopped. brushed.....), and
    a list of nouns/objects(park, store, bank, bed, breakfast, lunch, dinner, school....), and possibly some connectors..

    Put these on a form and using some combos or listboxes, create a series of terms
    eg "went to the store", "stopped at the bank"... that you could store in a table as individual records.

    Then use the table and records to populate a "consistent form" on which users could subsequently select appropriate phrases for whatever purpose you have.

    Just something to consider.

    Good luck with your project.

  10. #10
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by orange View Post
    I'm not sure where you want to use this set up, but if you are making a list of activities such that a person could select from the list and store the info, you might consider:

    a list of verbs (went, took, ate, ran,stopped. brushed.....), and
    a list of nouns/objects(park, store, bank, bed, breakfast, lunch, dinner, school....), and possibly some connectors..

    Put these on a form and using some combos or listboxes, create a series of terms
    eg "went to the store", "stopped at the bank"... that you could store in a table as individual records.

    Then use the table and records to populate a "consistent form" on which users could subsequently select appropriate phrases for whatever purpose you have.

    Just something to consider.

    Good luck with your project.
    It's funny, but the whole reason I'm doing this project is because someone wants a paragraph creator, so Access is not being used to actually store any of the information, but rather for the sole purpose of creating these paragraphs based on user options. An option I'm toying with right now is just a set of tickboxes. If it's ticked, I'm going to concatenate the value represented by that box, and create the paragraph that way. Not as fun as having multi-select, but a lot less aggravating, I think. Just gotta figure out how to loop all these in as my VBA skills are limited. I appreciate everyone's help!

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sounds like a much better solution.
    Something like this where you have checkboxes called chk1, chk2 etc and corresponding textboxes txt1, txt2 containing your text strings

    Code:
    Dim strText As String, I As Integer, N As Integer
    
    strText=""
    N = number of checkboxes e.g. 10
    
    For I = 1 to N
       If Me("chk" & I)=True Then strText= strText & ", " & Me("txt" & I)
    Next

  12. #12
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by ridders52 View Post
    Sounds like a much better solution.
    Something like this where you have checkboxes called chk1, chk2 etc and corresponding textboxes txt1, txt2 containing your text strings

    Code:
    Dim strText As String, I As Integer, N As Integer
    
    strText=""
    N = number of checkboxes e.g. 10
    
    For I = 1 to N
       If Me("chk" & I)=True Then strText= strText & ", " & Me("txt" & I)
    Next
    This is perfect! Is there any way to make N a dynamic number? And please correct me if I'm wrong, I would also need to figure out a way to dynamically add checkboxes (which I don't think should be too troubling, I hope).

    I find that a simple solution (for a novice like me) would be to create a bunch of extra checkboxes that are hidden and add a button. When pushed, it will reveal the next checkbox. I will also have that particular button then be hidden and replaced with another button that, when pushed, could reveal the next hidden checkbox. I realize this isn't the most elegant solution, but it'll do for the time being. I hate to keep asking for more help (you've all been excellent so far!) but if anyone has a better solution, I'm absolutely all ears! Playing with your solution above, @ridders52. Thank you!!!

  13. #13
    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
    someone wants a paragraph creator
    Depending on what this really entails you might want to check this link re Generating Paragraphs.

    Many years ago I work on an application that dealt with sending focused letters to people who picked up a travel flyer with info on travel vacations, resorts, hobbies, skiing, fishing.... The flyer was a pre-addressed card where the user could fill in their name/address and select from a number of topics (with check marks) and return it to a specific address. The names and addresses and their selected check boxes were matched with a paragraph an lines file that created a form letter, customized with salutation, intro para re thank you for your interest, followed by a number of lines of information specific to the subjects they had checked. There was a template for the generic letter, but the paragraphs and lines were assembled to make a focused letter to the person --all printed on high speed printer with a signature and enclosed in a windowed envelop and "bulk mailed".

  14. #14
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by Ramtrap View Post
    Hello everyone,

    I have a multi-select combo box whereby I need to create an array from information that was clicked and create a sentence, in order, from the way the array was input.

    Here's what I had after doing a bunch of testing last week:

    Code:
    Private Sub Command3_Click()
     
    Dim text2 As String
    Dim output2 As String
    Dim item2 As Variant
     
    'Fill a string with data which is semi-colon delimited; test by uncommenting one at a time.
    'text2 = "Allison"
    'text2 = "Allison;Brent"
    'text2 = "Allison;Brent;Carl"
    'text2 = "Allison;Brent;Carl;Darryl"
    text2 = "Allison;Brent;Carl;Dan;Ed;Fred;Gail;Hank;Ichabad;Jack;Kermit"
    
    
    '*****ADDITIONAL INFO HERE******
    'Dim the array
    Dim names2() As String '<--Dynamic Array (did not set an array boundary in the ()s
    '*****ADDITIONAL INFO HERE******
     
    'Split the text and put it into a dynamic array
    names2() = Split(text2, ";")
    
    output2 = Join(names2, ",")
      
    'VBA for appropriate commas and 'and'
    Dim result As String
    For i = 0 To UBound(names2())
        If UBound(names2()) = 0 Then
            result = names2(0) & " is here alone."
        ElseIf UBound(names2()) = 1 Then
            result = names2(0) & " and " & names2(1) & " are here together."
        Else
            If i = UBound(names2()) Then
                result = result & names2(i) & " were all here."
            ElseIf i = (UBound(names2()) - 1) Then
                result = result & names2(i) & ", and "
            Else
                result = result & names2(i) & ", "
           End If
        End If
    Next
    MsgBox result
     
    End Sub
    I understand how it's looping through the pre-provided string (text2), but I need the ability to actually create that string, myself. Example, if I have the below combo box with the options selected, I want to be able to spit the information back out as: ..."went to the park, watched TV, took a shower, went to bed"...
    Click image for larger version. 

Name:	Verbs.PNG 
Views:	30 
Size:	8.6 KB 
ID:	33192

    I know how, from the VBA, above, to hand-jam that text string, change the delimiter, and get the desired output, but what I don't know is how to identify the boxes that are checked and to insert those with a -1 value into that dynamic array. I'm certainly not looking for anyone to do my work for me, but if anyone has a good link on how to create arrays based on the values entered in a multi-selectable combo box, I would certainly be thankful!

    Thank you!
    I edited my VBA above as I missed out (somehow!) on the Dimming of the Array.

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No longer sure which approach you are pursuing....

    Quote Originally Posted by Ramtrap View Post
    I edited my VBA above as I missed out (somehow!) on the Dimming of the Array.
    Quote Originally Posted by Ramtrap View Post
    This is perfect! Is there any way to make N a dynamic number? And please correct me if I'm wrong, I would also need to figure out a way to dynamically add checkboxes (which I don't think should be too troubling, I hope).

    I find that a simple solution (for a novice like me) would be to create a bunch of extra checkboxes that are hidden and add a button. When pushed, it will reveal the next checkbox. I will also have that particular button then be hidden and replaced with another button that, when pushed, could reveal the next hidden checkbox. I realize this isn't the most elegant solution, but it'll do for the time being. I hate to keep asking for more help (you've all been excellent so far!) but if anyone has a better solution, I'm absolutely all ears! Playing with your solution above, @ridders52. Thank you!!!
    You can set the value to whatever you want but you shouldn't try to add checkboxes dynamically therefore there is no need for N to be adjusted 'on the fly'
    Add as many checkboxes as you like in DESIGN view - you can hide some if you wish. Set the value of N in the form declarations area or form_load or whatever
    You can reveal one checkbox at a time if you wish but not sure its worth doing myself

    Orange mentioned a paragraph creator
    I used that idea over 10 years ago to create student references for a schools database based on a series on combobox alternative choices.
    It worked well.
    Where none of the available choices were appropriate for a particular student, free text was allowed so it could be individualised as necessary
    Here's a screenshot for a fictitious student:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	14 
Size:	185.8 KB 
ID:	33214
    Last edited by isladogs; 03-21-2018 at 01:28 PM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Create Array
    By jo15765 in forum Programming
    Replies: 9
    Last Post: 11-09-2016, 12:55 PM
  2. Passing dynamic name of array to a Function
    By mcarval22 in forum Programming
    Replies: 41
    Last Post: 07-29-2016, 04:53 PM
  3. Create objects from array or other
    By Ruegen in forum Programming
    Replies: 8
    Last Post: 03-25-2015, 05:43 AM
  4. Singly linked, dynamic array, or both?
    By kopbad in forum Database Design
    Replies: 9
    Last Post: 04-27-2012, 09:13 PM
  5. dynamic array for calendar
    By workindan in forum Programming
    Replies: 7
    Last Post: 11-12-2010, 01:20 PM

Tags for this Thread

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