Hi, I'm trying to get an SQL update to work with 2 criteria's however I can only get one of them working at any given time, any ideas?
This code works fine and finds the correct product.
Code:
DoCmd.RunSQL "UPDATE Inventory SET BarsGiven=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID
This code works fine and finds the correct order.
Code:
DoCmd.RunSQL "UPDATE Inventory SET BarsGiven=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID
This code doesn't work, no matter which order I put the criteria's it says "Compile Error: Variable not defined" I've tried many combinations with commas etc but can't get it working.
Code:
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"
Perfect, thank-you! I tried so many combinations too.
I have a subform2 set as continuous with multiple records, there's another subform1 setup the same way and shows the same records. These records share a productID.
I need all records on the subform1 to update but at present its only using the first record on the subform2 I'm calling the numbers from.
I'm not really sure what I need to do so it updates all records in subform1 according to the matching order in subform2. In the sql update you helped me with, when I ran it with just OrderID as criteria it would update all 3 records in the table with the number from the first record in the subform2. So with my very little experience I'm thinking I need to setup a recordset, is that correct?
Well, most efficient would be the SQL, if you can find the right criteria. Is there a way of identifying those 3 records without having to include something unique to just one? You said they share productID. I assume that alone doesn't get all 3 (or limit itself to those 3)? Are there master/child links for the subform that would identify the 3?
If you need to know something unique about each of the 3 individually, you are probably stuck with a recordset.
The right criteria could be tough, subform1 and subform2 share ProductID, Subform1 shares OrderID with OrderSubform but subform2 has no OrderID and can't because it comes from a different source all together. Subform1 always gets propulated first and those are the records I need to update. Next subform2 may get populated and if it does it needs to match record for record with an update and at this point we'll say they will always be the same records which they won't but I'll get to that later.
For now I'm trying to make an update that will do product for product between the 2 subforms and later need to make it so if a product is in subform1 and in subform2 it updates and if subform2 has a product subform1 doesn't have it needs to insert, but that's another day.
I've been playing with recordsets and am getting somewhere it seems but still not doing what I want. Maybe you can see something in my code thats wrong or needs changing, it seems like it needs some kind of loop to go from record to record automatically.
Using just the code you helped me with using both criterias it always grabs the top record in subform2 and uses that number always matching correctly. If the cursor is in say record 3 of subform1 it still does the first record.
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.
Code:
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 CasesPurchased=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID & " AND OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID
rs.MoveNext
Wend
rs.Close
