listbox ?

[Guest]

I am having a prblem with this script. the itemselected returns what appears
to me t
as the position of the item in the listbox i.e. 1,2,3 opposed to the values
i need although the display correctly in the listbox.

Private Sub Command24_Click()

Dim varItem As Variant
Dim strSql As String
Dim strIn As String

strSql = "INSERT INTO testing ( ID, DESCRIPTION, COMMODITY_CODE ) SELECT
dbo_PART.ID, dbo_PART.DESCRIPTION, dbo_PART.COMMODITY_CODE FROM dbo_PART
where commidty_code IN ("
'build list
For Each varItem In Me.COMMODITY_CODE_LISTBOX.ItemsSelected

strIn = strIn & varItem & ","
Next varItem


'remove trailing comma
strIn = Left(strIn, Len(strIn) - 1)

strSql = strSql & strIn & ")"

End Sub

 

[Dirk Goldgar]

I am having a prblem with this script. the itemselected returns what
appears to me t
as the position of the item in the listbox i.e. 1,2,3 opposed to the
values i need although the display correctly in the listbox.

Private Sub Command24_Click()

Dim varItem As Variant
Dim strSql As String
Dim strIn As String

strSql = "INSERT INTO testing ( ID, DESCRIPTION, COMMODITY_CODE )
SELECT dbo_PART.ID, dbo_PART.DESCRIPTION, dbo_PART.COMMODITY_CODE
FROM dbo_PART where commidty_code IN ("
'build list
For Each varItem In Me.COMMODITY_CODE_LISTBOX.ItemsSelected

strIn = strIn & varItem & ","
Next varItem


'remove trailing comma
strIn = Left(strIn, Len(strIn) - 1)

strSql = strSql & strIn & ")"

End Sub

ItemsSelected returns a collection of row numbers (in the list), not the
values in the list for those rows. Try this:

With Me.COMMODITY_CODE_LISTBOX
For Each varItem In .ItemsSelected
strIn = strIn & .ItemData(varItem) & ","
Next varItem
End With