Working on an SQL Insert / Update combined

[robsworld78]

Hi, I have a bit of an issue with SQL Update / Insert.

I have a form that runs an SQL insert when a field is popluated and it works fine. When this sql insert runs it can add anywhere from 1 new record to 50 new records depending whats on the form.

DoCmd.RunSQL "INSERT INTO Inventory (Product, OrderID, BarsLeftTemp, BarsGiven) " & _
"SELECT ProductID, " & [Forms]![CreateOrders]![OrdersOrdersSubform]![OrderID] & " AS OrderID, " & "BarsLeft, " & "BarsLeft " & _
"FROM VendorInventoryLevel " & _ "WHERE (BarsLeft>0 Or BarsLeft<0) AND VendorID=" & [Forms]![CreateOrders]![VendorID] & ";"

The insert is for details pertaining to an order. Now I have a button on the same form and when clicked I want it to run another SQL insert/update but don't know how to right the code.

The 2nd insert will contain exisiting records from the 1st insert as well as new records so it needs to update the existing and insert the new records, I can't have duplicates.

Example:

I start a new order and the 1st SQL insert runs and I get the following items.

Order# 235 (stored in orders table)

item1 - 10 (stored in ordersdetail table linked with orderID) (10 being quanity)
item2 - 10
item3 - 10
item4 - 10

Now when I press the button, if I have SQL insert like above it would do the following, if the quantity was 5 on these items. (it duplicates items)

item1 - 10
item1 - 05
item2 - 10
item3 - 10
item3 - 05
item4 - 10
item4 - 05
item5 - 05

and if i press the button with SQL update it does the following (only updates, didn't add item5)

item1 - 10, 05
item2 - 10,
item3 - 10, 05
item4 - 10, 05

I hope this makes sense in the end the final list should be as follows because it updated the items already on the order and inserted items not on the order, in this case being item5.

item1 - 10, 05
item2 - 10,
item3 - 10, 05
item4 - 10, 05
item5 - 05

 

[DJkarl]

There is no one query type that can do what you want, you need to run 2 seperate queries as you're doing, one for the update, one for the insert. If you don't want duplicates either key the table to prevent them, or join the table to itself in the insert query and only insert records where the existing item is null in the table.

 

[robsworld78]

Well, I'm happy I figured it out. Not the cleanest solution but its what I came up with and it works.

Since my original code did work and just didn't do all the records I thought about playing with GoToRecord and SetFocus. Now I have it so the 2nd subform setsfocus and goes to first record, then the SQL update I have gets run, then it goes to next record and the update is run again, then to next record and so on. Of course it does nothing when it focuses on a record not in the new order and continues so that's good. Once it goes through all the records on subform 2 it runs an SQL update which comes from a query. I was able to make 3 queries, one has all the records from the 1st subform and the 2nd query has all the records from the 2nd subform, then on the 3rd query it only shows the records not on the 1st subform and inserts those remaining records.

Its all invisible and instant, I'm very happy it's working. Thanks for you help if you have any suggestions to improve this let me know. Here's the code.

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

Forms!CreateOrders!CreateOrdersVansSetForTransfer.SetFocus
Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!VendorID.SetFocus
On Error Resume Next
DoCmd.GoToRecord , , acFirst
DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " &  Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product  = " &  Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " &  Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product  = " &  Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " &  Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product  = " &  Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " &  Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product  = " &  Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " &  Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product  = " &  Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " &  Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product  = " &  Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " &  Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product  = " &  Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " &  Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product  = " &  Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

DoCmd.RunSQL "INSERT INTO Inventory (Product, OrderID, BarsGivenVan) " & _
"SELECT ProductID, " &  [Forms]![CreateOrders]![OrdersOrdersSubform]![OrderID] & " AS  OrderID, " & "BarsLeft " & _
"FROM CreateOrdersVanTransferAppendQueryMain "
DoCmd.SetWarnings True

Me.Requery