Use values from list box to append to a table

[postmanplod]

I have a list box and have set the multi select property to Extended. How do I append the values that a user selects from a list box to a table of my choice?

Thanks,

Michael

 

[PHV]

Use the ItemsSelected collection of your ListBox object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[postmanplod]

I want it associated to a command button - what code would I need to use?

 

[MajP]

Public Sub insertItems()
Dim lst As Access.ListBox
Dim itm As Variant
Dim strInsert As String
Dim strValues As String
Dim strSql As String
Dim colOne As Integer
Dim colTwo As String
'add more columns as needed
Set lst = Me.lstSup
strInsert = "Insert into tblName (field1Name,field2Name) values "
For Each itm In lst.ItemsSelected
colOne = lst.Column(0, itm)
colTwo = lst.Column(1, itm)
'modify the strValues for each column. Dates go in #date#, strings in single quote
strValues = "(" & colOne & ",'" & colTwo & "')"
strSql = strInsert & strValues
CurrentDb.Execute strSql
Next itm
End Sub

 

[postmanplod]

I only have to append one field and it is numeric.

I changed the code this:

Private Sub Command13_Click()
Dim lst As Access.ListBox
Dim itm As Variant
Dim strInsert As String
Dim strValues As String
Dim strSql As String
Dim colOne As Integer

'add more columns as needed
Set lst = Me![lstSerial]
strInsert = "Insert into [tblDisc]![Serial] values "
For Each itm In lst.ItemsSelected
colOne = lst.Column(0, itm)
'modify the strValues for each column. Dates go in #date#, strings in single quote
'strValues = "(" & colOne & ",'" & colTwo & "')"
'strSql = strInsert & strValues
CurrentDb.Execute strSql
Next itm
End Sub


But now receive the following error:

run time error 6 over, it then highlights:
colOne = lst.Column(0, itm)

Thank you in advance for any help.

 

[dhookom]

Learn how to debug your errors. Try faq705-7148.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Overflow errors occur when you exceed the limits of the variable dimension

Dim colOne As Integer

Integer
The Integer data type is a 16-bit number which can range from -32768 to 32767. Integers should be used when you are working with values that can not contain fractional numbers.

• Long
The Long data type is a 32-bit number which can range from -2,147,483,648 to 2,147,483,647. Long variables can only contain non-fractional integer values. I myself use Long variables over Integers for increased performance. Most Win32 functions use this data type for this reason.

• Single
The Single data type is a 32-bit number ranging from -3.402823e38 to -1.401298e-45 for negative values and from 1.401298e-45 to 3.402823e38 for positive values. When you need fractional numbers within this range, this is the data type to use.

This is not correct also
strInsert = "Insert into [tblDisc]![Serial] values