By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,081 Members | 2,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,081 IT Pros & Developers. It's quick & easy.

Adding multiple values in a listbox to a table

P: 22
Hi

I have a form with a few textboxes and some listboxes with multiple values allowed. I would like to use an append query to insert everything into a table but for some reason it just gives me no values at all for the listboxes (the textboxes are ok). Is there something that could be done.

Thanks in advance
Jan 17 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi

I have a form with a few textboxes and some listboxes with multiple values allowed. I would like to use an append query to insert everything into a table but for some reason it just gives me no values at all for the listboxes (the textboxes are ok). Is there something that could be done.

Thanks in advance
To retrieve values from a multiselect listbox:

Expand|Select|Wrap|Line Numbers
  1. Dim valSelect As Variant
  2. Dim strValue As String ' just used for the demonstration
  3.  
  4.    For Each valSelect In Me.listboxName.ItemsSelected
  5.       strValue = strValue & ", '" & Me.listboxName.ItemData(valSelect) & "'"
  6.    Next valSelect
  7.  
  8.    ' to remove last comma
  9.    strValue = Left(strValue, Len(strValue)-2)
  10.  
Mary
Jan 18 '07 #2

NeoPa
Expert Mod 15k+
P: 31,494
To retrieve values from a multiselect listbox:

Expand|Select|Wrap|Line Numbers
  1. Dim valSelect As Variant
  2. Dim strValue As String ' just used for the demonstration
  3.  
  4.    For Each valSelect In Me.listboxName.ItemsSelected
  5.       strValue = strValue & ", '" & Me.listboxName.ItemData(valSelect) & "'"
  6.    Next valSelect
  7.  
  8.    ' to remove last comma
  9.    strValue = Left(strValue, Len(strValue)-2)
  10.  
Mary
There's a small bug with this in that there are two common ways of stripping off the extra ',' and this mixes the two. It adds the extra comma at the front but strips it from the rear. I prefer the front way (oo er) because the code to strip it is shorter, but both ways work.
Expand|Select|Wrap|Line Numbers
  1. Dim valSelect As Variant
  2. Dim strValue As String ' just used for the demonstration
  3.  
  4.     For Each valSelect In Me.listboxName.ItemsSelected
  5.         strValue = strValue & ", '" & Me.listboxName.ItemData(valSelect) & "'"
  6.     Next valSelect
  7.  
  8.     ' to remove leading comma
  9.     strValue = Mid(strValue, 3)
Jan 18 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
There's a small bug with this in that there are two common ways of stripping off the extra ',' and this mixes the two. It adds the extra comma at the front but strips it from the rear. I prefer the front way (oo er) because the code to strip it is shorter, but both ways work.
Expand|Select|Wrap|Line Numbers
  1. Dim valSelect As Variant
  2. Dim strValue As String ' just used for the demonstration
  3.  
  4.     For Each valSelect In Me.listboxName.ItemsSelected
  5.         strValue = strValue & ", '" & Me.listboxName.ItemData(valSelect) & "'"
  6.     Next valSelect
  7.  
  8.     ' to remove leading comma
  9.     strValue = Mid(strValue, 3)
Sorry that was my error, meant to place the comma at the end not the front. Good catch Ade.
Jan 18 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Revised code doing it my way ...


Expand|Select|Wrap|Line Numbers
  1. Dim valSelect As Variant
  2. Dim strValue As String ' just used for the demonstration
  3.  
  4.     For Each valSelect In Me.listboxName.ItemsSelected
  5.         strValue = strValue & "'" & Me.listboxName.ItemData(valSelect) & "', "
  6.     Next valSelect
  7.  
  8.     ' to remove leading comma
  9.     strValue = Left(strValue, Len(strValue)-2)
  10.  
Jan 18 '07 #5

Post your reply

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