469,338 Members | 8,430 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

Saving list box items as a string

15
Is there anyway to save multiple items selected in a list box to a string? Is it then possible to save the string to a record field in a table? Or is there another way to capture multiple items selected and stored them in a table? For example: I have a list box with the following items:
Listbox
cat
dog
rabbit

Table name = Animal_tbl
Animal Selected - this is a column heading and a text field.
Dec 28 '10 #1
6 10043
ADezii
8,800 Expert 8TB
Is there anyway to save multiple items selected in a list box to a string?
Yes there is but:
  1. What is your reason for storing multiple, selected Objects from a Multi-Select List Box to a Field in a Table?
  2. How do you wish the String to appear?
    Expand|Select|Wrap|Line Numbers
    1. cat,dog,rabbit
    2. cat dog rabbit
    3. cat-dog-rabbit
    4. etc...
Dec 29 '10 #2
MarkP
15
I'm trying to store multiple values in a field to reduce the amount of check boxes in a table. My present table has over 75 columns that are used to received check boxes from a form. But I need to capture each record/event from the user. So each record is unique and will be used in trending analysis. If there is another way that I can use to reduce the number of columns in a table then I will be open to that suggestion. Thank you and Happy New Year.
Dec 29 '10 #3
MarkP
15
Sorry about not answering your last question. I was hoping to store the string with comas like in your first example above.
Dec 29 '10 #4
ADezii
8,800 Expert 8TB
Assuming your List Box is named lstAnimals:
Expand|Select|Wrap|Line Numbers
  1. Dim ctl As ListBox
  2. Dim varItm As Variant
  3. Dim strBuild As String      'To build Animal Selected List
  4.  
  5. Set ctl = Me![lstAnimals]
  6.  
  7. If ctl.ItemsSelected.Count < 1 Then Exit Sub
  8.  
  9. For Each varItm In ctl.ItemsSelected
  10.   strBuild = strBuild & ctl.ItemData(varItm) & ","
  11. Next varItm
  12.  
  13. strBuild = Left$(strBuild, Len(strBuild) - 1)
  14.  
  15. CurrentDb.Execute "INSERT INTO Animal_tbl ([Animal Selected]) VALUES " & _
  16.                   "('" & strBuild & "')", dbFailOnError
Sample Results:
Expand|Select|Wrap|Line Numbers
  1. Animal Selected
  2. Rabbit
  3. Cat,Lion
  4. Cat,Dog,Rabbit,Lion
  5. Cat,Dog,Rabbit,Mouse,Lion
  6. Dog
  7. Dog,Rabbit,Mouse
Dec 29 '10 #5
MarkP
15
Thank you for that. You should change your hobby (database programming) to the day job. But either way I'm glad you read my message.
Dec 29 '10 #6
ADezii
8,800 Expert 8TB
Some things are fun as long as they are a hobby - once they become a job, then they are a burden! (LOL).
Dec 29 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Peter Collinson | last post: by
2 posts views Thread by Unknown User | last post: by
9 posts views Thread by shapper | last post: by
reply views Thread by c0mrade | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.