INSERT INTO to create new record from List Box value

[Storyteller]

Hello All,

I am creating a Purchase Order form that uses a list box to show the BarcodeNumber, Description and WholeSaleCost of a Product using this:

SELECT [tblProducts].[BarCodeNumber], [tblProducts].[Description], [tblProducts].[WholeSaleCost] FROM tblProducts WHERE ((([tblProducts].[SupplierID])=[Forms]![frmPurchaseOrders]![POSupplierID])) ORDER BY [tblProducts].[BarCodeNumber];

The List box works properly. I have written the following code to loop through the list box items selected:

Private Sub cmdAddToPO_Click()
' loop through the list box
' whatever records have been selected
' add to tblPurchaseOrderDetails
Dim ctlList As Control
Dim varItem As Variant
Dim intI As Integer
Dim Test As Variant

Set ctlList = Me.List0

For Each varItem In ctlList.ItemsSelected
For intI = 0 To ctlList.ColumnCount - 1
Test = ctlList.Column(intI, varItem)
Debug.Print Test
Next intI
DoCmd.RunSQL "INSERT INTO tblPurchaseOrderDetails ('POBarcodeNumber', 'PODescription', POUnitPrice) VALUES (Test);"
Next varItem
End Sub

I get the following error message: "Number of Query values and Destination Fields are not the same" when I run the code. The Immediate Window shows that the proper values are being used.
Example:
0102709J83
Switch assembly, Astro Spectra vol pot, ch. Sw. & Flex
35.1

tblPurchaseOrderDetails has the following fields:
POID - Number
POBarcodeNumber - Text
PODescription - Text
POUnitPrice - Number
POQuantity - Number

The idea is that the User can select 1 or more items from the listbox click the add button the items selected would then show up in the subformPurchaseOrderDetails (RecordSource: tblPurchaseOrderDetails) and then the User could enter in the Quantity required.

Any suggestions would be greatly appreciated.

Regards,
Michael

 

[SQLSister]

INSERT INTO tblPurchaseOrderDetails ('POBarcodeNumber', 'PODescription', POUnitPrice) VALUES (Test);"
you are inserting one value but you specified 3 columns


Questions about posting. See faq183-874

 

[Storyteller]

Hello SQLSister,
Thanks for your quick response. I modified the SQL statement to: "INSERT INTO tblPurchaseOrderDetails ('POBarcodeNumber')VALUES (Test);"

When I run the code I am prompted for a parameter value "Test"

I was under the impression that INSERT INTO . . . VALUES could accomodate multiple values.

I'll keep working on it and keep an eye on this post.

Regards,
Michael

 

[john76]

Try modifying your code to:

...
DoCmd.RunSQL "INSERT INTO tblPurchaseOrderDetails (POBarcodeNumber) VALUES ([red]"+[/red]Test[red]+"[/red]);"
...

A few notes:
1. I removed the single quotes from around POBarcodeNumber in the INSERT statement as they are incorrectly placed.
2. You may need to convert the Test variable from Variant to whatever type POBarcodeNumber is, ie: int, char, etc.
3. You can insert multiple values in a single select, but you must specify the values to be inserted in the query. For instance:

INSERT INTO tblPurchaseOrderDetails (POBarcodeNumber, PODescription, POUnitPrice) VALUES (12345, 'MyPODescription', 199.99)

HTH,
John

 

[Storyteller]

Hello John,

I followed your first instructions and I no longer get the parameter prompt. Thanks.

Interesting thing happens, the value is INSERTED not into the current PO, but to the first PO. I assumed (oops) that it would do that. Me thinks a WHERE statement is required.

I'm going to keep on working on this. But, I think I will also try a different tact with this PO form.

Regards,
Michael