SQL Insert / Update Merged (Video)

[robsworld78]

Hi, I'm in need of help with coding an SQL insert / update, either one needs to run depending if a product exists for an orderID.

I uploaded a video to youtube so you can see exactly what I'm trying to accomplish. It's best to watch the video before reading any further.

http://youtu.be/pXgyGJnHuOE

Here's the code I'm using for the 2 SQL inserts.

This is the insert for moving the bin inventory, it happens when the freezer is selected, I said in the video when I selected the unit however that's when it refreshes the inventory subform so the insert is actually visible. That doesn't change anything though and that's why I can change the unit as much as I want and it doesn't rerun the insert, the reason I put it on the freezer and disabled it after selecting one.

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

And here's the code that runs when I click the "Give me inventory" button to move the van inventory.

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

Any ideas would be helpful.
Thanks

[orange]

You might want to look into the db.execute method with the dbFailOnError option
http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

This will save using the SetWarnings .

You should have Error Handling in all of your code (you probably do already).

I'm not sure what this line is doing, or if it's needed

DoCmd.RunCommand acCmdSaveRecord

For debugging purposes, (comment out your db.execute) create all of your sql as strings, use a Debug.print to display the actual sql to the immediate window during testing; once you're happy you can comment the Debug.Prints and activate/uncomment the db.execute.

For this (BarsLeft>0 Or BarsLeft<0) you could say Barsleft<>0

[robsworld78]

Skip this post

[orange]

I'm heading out at the moment but look at this site especially the warning
http://www.w3schools.com/sql/sql_update.asp

To test you query, you can use the query wizard.
Change the type of query from update to select query and see what the result is,
then change the query type to Update when you have the where clause worked out.
You can test these without vba. Just copy the sql and make sure the syntax is ok for the query wizard.

There's probably some videos on youtube that show you how to test an update query by temporarily making it a select query.

[robsworld78]

I removed my other post so not to confuse people.

For now I'm trying to work out an SQL Update that will update all the products on subform1 from subform2.

Subform1 and subform2 both share ProductID and have the same records on each subform. When I use just the following code it always grabs the top record in subform2 and uses that number always matching the product. If the cursor is in say record 3 of subform1 it still does the first record.

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

When I add my recordset code it now copies whichever record I'm sitting on, so if I'm on record1 it uses record1, if I'm on record2 it updates record2, etc.. great! It now also asks me if I want to update the 1 record as many times as there are records. If there are 3 records it asks 3 times, if I hit yes everytime it only updates the record I'm sitting one.

So now it knows how many records there are and will update according to the record I'm on. I have 3 records in the subform2, if I sit on record1 and hit the button to run the code it updates record1, then I move to record2 and hit the button and it updates record2, then I move to record3 and it updates record3, all done correctly, but I need to do it in an certain pattern.

When I first hit the button to run the code as I said it asks 3 times or as many records are in subform2. If I hit yes to all the updates and then press the button again nothing will happen. I need to close form and start over for code to work again. If I hit no after the first update, then move to next record the and press the button the code runs again, this time it will only ask 2 times if I want to update because 2 records still need updating the recordset (I'm guessing). If I hit no after the 2nd update and move to record3 the code will run but this time it won't ask more then once because its the last record to be updated, so it is tracking whats updating in a sense.

But how do I get it to loop so it does all 3?

Here's my code with the recordset.

With Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form.Recordset
.FindFirst "ProductID = " & Forms!CreateOrders!OrdersInventorySubform.Form.ProductID
If .NoMatch Then
MsgBox "Record ID " & Forms!CreateOrders!OrdersInventorySubform.Form.ProductID & " not found!"
End If
End With

Dim rs As DAO.Recordset
Set rs = Me!CreateOrdersVansSetForTransfer.Form.RecordsetClone
While Not rs.EOF

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

rs.MoveNext
Wend
rs.Close

Thanks!