Adding multiple values in a listbox to a table

[Phille]

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

[MMcCarthy]

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:

Dim valSelect As Variant
Dim strValue As String ' just used for the demonstration
 
   For Each valSelect In Me.listboxName.ItemsSelected
      strValue = strValue & ", '" & Me.listboxName.ItemData(valSelect) & "'"
   Next valSelect
 
   ' to remove last comma
   strValue = Left(strValue, Len(strValue)-2)
 

Mary

[NeoPa]

To retrieve values from a multiselect listbox:

Dim valSelect As Variant
Dim strValue As String ' just used for the demonstration
 
   For Each valSelect In Me.listboxName.ItemsSelected
      strValue = strValue & ", '" & Me.listboxName.ItemData(valSelect) & "'"
   Next valSelect
 
   ' to remove last comma
   strValue = Left(strValue, Len(strValue)-2)
 

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.

Dim valSelect As Variant
Dim strValue As String ' just used for the demonstration
 
    For Each valSelect In Me.listboxName.ItemsSelected
        strValue = strValue & ", '" & Me.listboxName.ItemData(valSelect) & "'"
    Next valSelect
 
    ' to remove leading comma
    strValue = Mid(strValue, 3)

[MMcCarthy]

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.

Dim valSelect As Variant
Dim strValue As String ' just used for the demonstration
 
    For Each valSelect In Me.listboxName.ItemsSelected
        strValue = strValue & ", '" & Me.listboxName.ItemData(valSelect) & "'"
    Next valSelect
 
    ' to remove leading comma
    strValue = Mid(strValue, 3)

Sorry that was my error, meant to place the comma at the end not the front. Good catch Ade.

[MMcCarthy]

Revised code doing it my way ...

Dim valSelect As Variant
Dim strValue As String ' just used for the demonstration
 
    For Each valSelect In Me.listboxName.ItemsSelected
        strValue = strValue & "'" & Me.listboxName.ItemData(valSelect) & "', "
    Next valSelect
 
    ' to remove leading comma
    strValue = Left(strValue, Len(strValue)-2)