Insert into table not working

[Peter via AccessMonster.com]

I have the following code to insert multiple select values from a listbox
into a table, but it is bombing out on the last SQL statement. Can someone
help me with this?

Thanks,
Peter


Private Sub Add2TblBtn_Click()

Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim txtList, strSQL As String


Set frm = Forms!Form1
Set ctl = frm!ListBox1
For Each varItm In ctl.ItemsSelected
‘ I think the below is putting the multi select items in txtlist
txtList = txtList & "," & ctl.ItemData(varItm)
Next varItm
strSQL = "INSERT INTO CustListbox([cust_ID]),[SelectedItems]) SELECT " & Me.
ListBox1.Column(0) & "," ' & txtList & '","
DoCmd.RunSQL strSQL

 

[Jeff Boyce]

Peter

"... bombing out ..." could mean many different things.

Do you have any additional clues for us?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kipp Woodard]

Peter, put a break in your code and examine the value of strSQL before
executing it. At this point you can take the SQL string to a query window
and see what is wrong with it.

I think I see the issue, but I can't be sure without seeing the entire
constructed string.

Bad: strSQL = "INSERT INTO CustListbox([cust_ID]),[SelectedItems]) SELECT "
Good: strSQL = "INSERT INTO CustListbox ([cust_ID] ,[SelectedItems]) SELECT "

 

[Peter via AccessMonster.com]

Jeff,
I'm trying now a straight insert to the table. FirmListbox is the table and
ListBox1 is the listbox
I'm trying to get the four items "firm_id, firm_name, contact and firm_note"
values into the table from the multi select list box. the error is now "the
number of query values and destination are not the same. Here's my code
below.


Dim db As Database: Set db = CurrentDb()
Dim VarItem As Variant

For Each varItem In Me.ListBox1.ItemsSelected
db.Execute"INSERT INTO FirmListbox(Firm_ID,Firm_Name,Contact,Firm_Note)
VALUES("&_
Me.ListBox1.ItemData(varItem)&")"
Next VarItem

Peter

"... bombing out ..." could mean many different things.

Do you have any additional clues for us?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have the following code to insert multiple select values from a listbox
into a table, but it is bombing out on the last SQL statement. Can

[quoted text clipped - 20 lines]

ListBox1.Column(0) & "," ' & txtList & '","
DoCmd.RunSQL strSQL

 

[Peter via AccessMonster.com]

Ok, I got it!
It is writing to the table, but the note field is truncated. The note field
is a memo field. How do I get it to write the entire note field to the table?

Peter, put a break in your code and examine the value of strSQL before
executing it. At this point you can take the SQL string to a query window
and see what is wrong with it.

I think I see the issue, but I can't be sure without seeing the entire
constructed string.

Bad: strSQL = "INSERT INTO CustListbox([cust_ID]),[SelectedItems]) SELECT "
Good: strSQL = "INSERT INTO CustListbox ([cust_ID] ,[SelectedItems]) SELECT "

I have the following code to insert multiple select values from a listbox
into a table, but it is bombing out on the last SQL statement. Can someone

[quoted text clipped - 18 lines]

ListBox1.Column(0) & "," ' & txtList & '","
DoCmd.RunSQL strSQL