Append more then once into temp table 2

[techi1961]

I need to append a single record of a subform named Delivery Dockets on a form called Jobs to a temp table named Delivery Dockets Temp more than once depending on the number entered in a field named "Pieces" in the subform. I currently have a button on the main form Jobs that currently calls this append query to add the record to the temp table. But I need it to append more then once depending on the number entered into the field named Pieces.

INSERT INTO [Delivery Docket Temp] ( DeliveryDocketID, JobID, ConsignorID, ConsigneeID, DeliveryDocketNo, PurchaseOrderNo, SpecNo, Pieces, GoodsDescription, Weight, DeliveryDocketDate, CollectionDate, DeliveryDate, OrderedBy, CustomerID, DeliveryTime, DANNumber, InStockDate, Delivered, P )
SELECT [Delivery Dockets].DeliveryDocketID, [Delivery Dockets].JobID, [Delivery Dockets].ConsignorID, [Delivery Dockets].ConsigneeID, [Delivery Dockets].DeliveryDocketNo, [Delivery Dockets].PurchaseOrderNo, [Delivery Dockets].SpecNo, [Delivery Dockets].Pieces, [Delivery Dockets].GoodsDescription, [Delivery Dockets].Weight, [Delivery Dockets].DeliveryDocketDate, [Delivery Dockets].CollectionDate, [Delivery Dockets].DeliveryDate, [Delivery Dockets].OrderedBy, [Delivery Dockets].CustomerID, [Delivery Dockets].DeliveryTime, [Delivery Dockets].DANNumber, [Delivery Dockets].InStockDate, [Delivery Dockets].Delivered, [Delivery Dockets].P
FROM Jobs INNER JOIN [Delivery Dockets] ON Jobs.JobID = [Delivery Dockets].JobID
WHERE ((([Delivery Dockets].JobID)=[Forms]![Jobs]![JobID]) AND (([Delivery Dockets].P)=Yes));


Any ideas would be greatly appreciated.

 

[trevil620]

In the 'On Click' event, just add some VBA code similar to the following:

Dim i as integer
Dim strSQL as string
For i = 1 to Me.Pieces
strSQL = "...your sql..."
Docmd.RunSQL strSQL
Next i

When all else fails, manipulate the data.

 

[techi1961]

@Trevil620,

Thank you for your reply
Sorry but I am not calling through the SQL method
I am using the DoCmd to run this append query from the main form

DoCmd.OpenQuery "AppendDeliveryTemp", acNormal, acEdit

How would I use your vba code with this?

Thanks again,

 

[PHV]

For i = 1 To Forms!Jobs![Delivery Dockets].Form!Pieces
  DoCmd.OpenQuery "AppendDeliveryTemp", acNormal, acEdit
Next

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

 

[techi1961]

@PHV
Thanks very much that did the trick....

 

[dhookom]

I still believe you should create a table of numbers [tblNums] with a single numeric field [Num] and values from 1 to 100. Then run this one query once:

INSERT INTO [Delivery Docket Temp] ( DeliveryDocketID, JobID, ConsignorID, ConsigneeID, DeliveryDocketNo,
 PurchaseOrderNo, SpecNo, Pieces, GoodsDescription, Weight, DeliveryDocketDate, CollectionDate, DeliveryDate, OrderedBy,
 CustomerID, DeliveryTime, DANNumber, InStockDate, Delivered, P )
SELECT [Delivery Dockets].DeliveryDocketID, [Delivery Dockets].JobID, [Delivery Dockets].ConsignorID, [Delivery Dockets].ConsigneeID,
 [Delivery Dockets].DeliveryDocketNo, [Delivery Dockets].PurchaseOrderNo, [Delivery Dockets].SpecNo, [Delivery Dockets].Pieces,
 [Delivery Dockets].GoodsDescription, [Delivery Dockets].Weight, [Delivery Dockets].DeliveryDocketDate, [Delivery Dockets].CollectionDate,
 [Delivery Dockets].DeliveryDate, [Delivery Dockets].OrderedBy, [Delivery Dockets].CustomerID, [Delivery Dockets].DeliveryTime,
 [Delivery Dockets].DANNumber, [Delivery Dockets].InStockDate, [Delivery Dockets].Delivered, [Delivery Dockets].P
FROM Jobs INNER JOIN [Delivery Dockets] ON Jobs.JobID = [Delivery Dockets].JobID[b][COLOR=#EF2929], tblNums[/color][/b]
WHERE [Delivery Dockets].JobID=[Forms]![Jobs]![JobID] AND [Delivery Dockets].P=Yes[b][COLOR=#EF2929] AND NUM <=[Pieces][/color][/b];

Duane
Hook'D on Access
MS Access MVP