SQL Update for multiple records

[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

[robsworld78]

Anyone know how I would add some better Error handle code to this.

Of course once it gets to the end of the records it pops up an error saying "no more records or whatever" but with "On Error Resume Next" it does continue through all the code without flashing an error however it would be nice when it hits the first error it stops processing the code and continues processing again once it gets to the SQL update near the end of the code, is that possible?