Insert Record From List box into table 2

[3239]

I am really struggling with this. I am trying to insert a selection from a list box into a table. I've looked for information on the web but nothing has worked for me.

Thanks

 

[PHV]

What have you tried so far and where in your code are you stuck ?

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

 

[missinglinq]

In addition to PH's question, exactly what are you trying to do? Your thread title "Insert Record From List box into table" would seem to indicate you're trying to place a record from one table into a second table, using the Listbox to facilitate this. Storing records in multiple places is usually a sign of a non-normalized database.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[3239]

What I have is a list box that displays data that is queried from an external database. What I am trying to do is insert data that is being pulled from the external database into a table based on my list box selection. I hope I am making sense. Please let me know.

Thanks

 

[randy700]

How about using an append query?


Randy

 

[3239]

I thought of using an append query but I don't know how code it to append a record based on the selection in the list box. In other words, I can't figure out how to get the append query to insert the record selected. Below is an example of some code I found on the web. I couldn't get it to work for me.

Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Dim TName As String
Dim IntI As Integer

Set frm = Forms!ItemSelect
Set ctl = frm!QuickSearch

For IntI = 0 To ctl.ListCount - 1
TName = ctl.ItemData(IntI)
If ctl.Selected(IntI) Then

'Add Record to TempReqItems Table


DoCmd.RunSQL "INSERT INTO RequisitionLineItems " & _
"( ProductName, POChartOfAccount, UnitPrice, UnitsOrdered, " & _
"Unit, RequisitionNumber ) " & _
"SELECT TempReqItems.ProductName, TempReqItems.AccountNumber, " & _
"TempReqItems.UnitPrice, TempReqItems.UnitsOrdered, TempReqItems.Unit, " & _
"TempReqItems.Requisition, *" & _
"FROM Query3 RIGHT JOIN TempReqItems ON Query3.RequisitionNumber = " & _
"TempReqItems.Requisition " & _
"WHERE (((Query3.POID)=[Forms]![POSearch]![POID]));"

End If
Next IntI

Thanks

 

[joelflorendo]

Not sure what your listbox structure is like but if it's multicolumn then you can use this:

Dim sSql as String

With myListBox
For each varItem In .ItemsSelected
sSql = "INSERT INTO myTable " & _ 
"(Field0, Field1, Field2,...)" & 
"VALUES('" & .Column(0,varItem) & "','" & .Column(1,varItem) & "','" & .Column(2,varItem) & "',...)"

Docmd.RunSQL sSql
Next
End With

If your field data is numeric, you'll have to remove the single quotes that surround that particular selected item's column.

 

[3239]

It seems like I'm getting somewhere now thanks joelflorendo but there is an error I'm getting. It is error: 3075 Syntax error in string in query expression "23.993". In the code below. "23.993" is the value of column 3. I'm new at writing sql code in vba so I don't quite understand the quotes yet.

Thanks for your help

Dim sSql As String
Dim varItem As Variant

With QuickSearch
For Each varItem In .ItemsSelected
sSql = "INSERT INTO RequisitionLineItems " & _
"( ProductName, POChartOfAccount, UnitPrice, UnitsOrdered, " & _
" RequisitionNumber ) " & _
"VALUES('" & .Column(0, varItem) & "','" & .Column(1, varItem) & "','" & .Column(2, varItem) & "','" & .Column(3, varItem) & .Column(4, varItem) & ""

 

[randy700]

It appears you're missing your final closing parenthesis.

Dim sSql As String
Dim varItem As Variant

With QuickSearch
For Each varItem In .ItemsSelected
sSql = "INSERT INTO RequisitionLineItems " & _
"( ProductName, POChartOfAccount, UnitPrice, UnitsOrdered, " & _
" RequisitionNumber ) " & _
"VALUES('" & .Column(0, varItem) & "','" & .Column(1, varItem) & "','" & .Column(2, varItem) & "','" & .Column(3, varItem) & .Column(4, varItem) & "[b][COLOR=red])[/color][/b]"

Randy

 

[3239]

Thank you very much Randy!! That worked!! Thanks to all of you for your help!!

 

[joelflorendo]

You might've already fixed this since you got it working but it looks like you're also missing a comma separator between the 3rd and 4th value statements:

Dim sSql As String
Dim varItem As Variant

With QuickSearch
For Each varItem In .ItemsSelected
sSql = "INSERT INTO RequisitionLineItems " & _
"( ProductName, POChartOfAccount, UnitPrice, UnitsOrdered, " & _
" RequisitionNumber ) " & _
"VALUES('" & .Column(0, varItem) & "','" & .Column(1, varItem) & "','" & .Column(2, varItem) & "'," & .Column(3, varItem) [COLOR=red]& ","[/color] & .Column(4, varItem) & ")"

Also, is UnitPrice a text field? If it's currency, you should remove the single quotes for that value statement as well.

 

[3239]

Yes I fixed it joelflorendo. Thank you for your help.