SQL Update for multiple records

[robsworld78]

Hi, I have a set of records I'm trying to run an update on, here's the code I'm using.

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

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

When that code runs only the record with focus gets updated, so I need to manual select each record, press the button and select another record until done, how can I make it so all the records update?

If I remove the productID from the WHERE clause it updates all the records for the order but it updates them all with the first record from the source.

I tried adding the SELECT and FROM to the code but it always brings up a runtime error saying syntax error in statement.

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenExtra=" & _
"SELECT ProductID, " & [Forms]![CreateOrders]![OrdersOrdersSubform]![OrderID] & " AS  OrderID, " & "BarsLeft " & _
"FROM InventoryLevelVansSetForTransfer " & _
"WHERE  OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

Any help would be great.

[aytee111]

What records are you trying to update? I am a bit confused as to your table structure - your Order number is on your Inventory table?

[robsworld78]

No, the order number is in the orders table and inventory table because they are linked together. The inventory table is actually "order details" I didn't name some things the best. So all the order info goes in the orders table such as date, status, etc... and all the details or product goes in the "inventory" table linked via orderID. Acutal inventory items are store in a table called "products" which should have been named "inventory"

I have 2 subforms for inventory, one subform is for the "new order details" continuous subform and this subform is linked to the "orders subform" via orderID so all records going in this subform count as child records to this parent order.

The other subform only shows records for inventory stored in a location, depending on location picked on the order the subform records can change, these records aren't linked to anything really.

Now when I create a new order at some point an SQL insert is ran and my new order gets autofilled with the products ready to go so a user doesn't need to manual select the items. So now my order has 3 new child records and is good to go but there is where I want to run an update.

The other subform we'll say has 4 records in it, 3 of them already exist in the new order and 1 doesn't. I want the update to run and update the 3 records I have in the "new order details" subform based on whats in the 2nd subform. With the code above it only updates one record not all records. Because I have a recordset between the 2 subforms it knows what product I have focus on and updates the product with the correct data but only the 1 product. So I my new order has 3 records I need to manual focus on each record and run the update 3 times. I've tried adding the rs.movenext which now cycles through all the records but always updates the record with focus. As in this order has 3 child records so it asks 3 times if it should run an update but only updates the record with focus 3 times.

While Not rs.EOF
DoCmd.RunSQL "UPDATE Inventory SET BarsGivenExtra=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID
rs.MoveNext
Wend
rs.Close

This is the code I use to create my recordset, I have it on the "new orders detail" subform under the "OnCurrent" event.

On Error Resume Next
With Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form.Recordset
On Error Resume Next
.FindFirst "Productid = " & Forms!CreateOrders!OrdersInventorySubform.Form.ProductID
If .NoMatch Then
End If
End With

Hope this makes sense, thanks.

[aytee111]

This is all getting very confusing - let's try and simplify it.

4 records in subform 2, 3 in subform 1

a) do you need to add the 4th record to the Inventory table?

b) the 3 records you are trying to update, all have the same OrderID - ?? and ProductID??

c) You are updating the BarsGivenExtra column on the Inventory table - for all three records, regardless of productID?

d) Are you trying to join the 3 records up to the 4 records and update Inventory with different values?

Sometimes if you write it out in words it will make sense - ie. do the analysis: "I need to update these records with this data under these circumstance"

[robsworld78]

I'm bad at explaining, it is simple and you do got it. Ultimately I do want that 4th record in there yes but for now I'm working on getting them all to update together.

b) Yes the 3 records I'm trying to update share the same orderID however they have different ProductID's because they are different items. The subform with the 4 records share the ProductID so the 3 records that are the same in both subforms have the same productID.

c) I'm trying to update BarsGivenExtra column in the Inventory table for all 3 records, data matching the 2nd subform.

d) Ultimately I want the 3 records to be the 4 records and field "BarsGivenExtra" to hold the number the 4 records had.

[aytee111]

I would do it all outside the form - forget trying to use the form data, it is only messing things up. If I have heard you correctly, all of the data already exists on tables and isn't being entered by the user.

Could it happen like this?
- user enters order number
- run query 1 - update matching records between subform 1 and subform 2 - make the record source of subform 2 an actual query (or is at already an existing table? - whatever), update the Inventory table directly, not thru the form
- run query 2 - append any missing records (unmatched query)
- re-display subform1 and subform2

Sometimes it is easier to do things the long way around - especially at first, until it is working. Afterwards we can streamline it and make it "pretty" if we want to.

[robsworld78]

that's what I'm trying to do, the form data is only for reference, the 2nd subform data is coming from a query that's why I was trying to use SELECT AND FROM in my original post and call the data from the query.

[aytee111]

So just do it! What is your question now?

[robsworld78]

ok I guess I don't understand what you mean outside the form, this does have to happen with a press of a button, using VBA

[aytee111]

- user enters order number
- AfterUpdate event vba code:
DoCmd.OpenQuery update query - "....WHERE OrderID=Forms!Formname!OrderID..." in query itself
DoCmd.OpenQuery append query
Me!subform1.requery
me!subform2.requery

[robsworld78]

I did play with a real update query but it wouldn't work it always told me to use a query that was editable. The query that shows the data on subform 2 isn't updateable, that seemed to be a problem even though I told it to update Inventory table but because the data was coming from that query it didn't work.

[aytee111]

Himm, yes. Thinking......

[robsworld78]

So why don't both WHERE conditions work in this code?

Dim rs As DAO.Recordset Set rs = Me.RecordsetClone  DoCmd.RunSQL "UPDATE Inventory SET BarsGivenExtra=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _ " WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

If I add

rs! in front of Forms!CreateOrders!CreateOrdersVansSetForTransfer. Form!BarsLeft from above it tells me nothing in the collection.

[aytee111]

The second criteria is on subform2, is that right? That is a recordset, maybe the pointer isn't set so there is no record that Access can actually use for the product id.

There is also this method - I haven't got it quite right but I would need your database to get it 100%. This would replace the update query, the rest would still be as above.

Dim rst As Recordset, strsql As String
strsql = "get the three records"
Set rst = CurrentDb.OpenRecordset(strsql)
Do Until rst.EOF
With rst
.Edit
rst("column name")=dlookup(get field from qry)
.Update
Loop

[robsworld78]

k thanks, I'll give it a try, have someone over now so I'll get back to it later. Thanks for your help