Update mutliple records in a single Table from mutliple rows in a subform

[LGrossman]

Hi All, I'm new to this forum (and forums full stop) so please forgive me if I'm breaking any forum etiquette...



I have a problem with an update query:-

I have a 3 tables: ProductTable, OrderTable and OrderProductTable. In the ProductTable I have 2 fields that I want to update: TotalStock and AllocatedStock.

I have an OrderForm that has an OrderProduct Subform so I can add as many Products to the order as needed.

Each time I add a new product and order quantity to the OrderProduct Subform it updates the The AllocatedStock field in ProductTable, and that works just fine.

The problem is when I want to confirm the entire order as complete and update the TotalStock and AllocatedStock fields in the ProductTable for each product on the OrderProduct Subform.

I can update the first product from the OrderProducts Subform but none of the subsequent ones. This is what I've done in the query design:-

Update To Columns: [ProductTbl]![TotalStockQty]-[Forms]![OrderFrm]![OrderProductSubform].[Form]![QtyTxt]
[ProductTbl]![AllocatedQty]-[Forms]![OrderEnquiryFrm]![OrderItemTbl subform].[Form]![QtyTxt]

Criteria Columns: ProductID in the ProductTable / Criteria: [Forms]![OrderFrm]![OrderProductSubform].[Form]![ProductIDTxt]
OrderID in the OrderTable / Criteria: [Forms]![OrderFrm]![OrderProductSubform].[Form]![EnquiryID]

I hope it makes sense, can anyone help please? It's driving me mad!

Thanks

[Micron]

Seems like you are expecting what you wrote to loop over each record in the subform and make the calculation. It won't - it will likely pick the first record if no record is selected or it will pick the current record. You'd need a calculated form control to contain the sums of the needed values and then subtract that. However, you're not going to like this next suggestion - which is to not do what you're doing

http://allenbrowne.com/AppInventory.html

[ranman256]

you would run 1 update query to update your table based on the query in the subform.

qsSubForm is the query in the subform
the upd query would be like:

update tInventory set tInventory .InvTotal =tInventory .InvTotal - qsSubform.Qty
where qsSubform.ItemID = tInventory.ItemID

[LGrossman]

Seems like you are expecting what you wrote to loop over each record in the subform and make the calculation. It won't - it will likely pick the first record if no record is selected or it will pick the current record. You'd need a calculated form control to contain the sums of the needed values and then subtract that. However, you're not going to like this next suggestion - which is to not do what you're doing

http://allenbrowne.com/AppInventory.html

Thanks for the advice and I guess in hindsight you're right, but I don't suppose you know of a way to loop through the subform records and update the product table? I have some coding knowledge but I struggle with looping.
If not then not to worry, I might have to re-think.

[kd2017]

As long as you're re-thinking consider looking up a few example inventory tracking models. http://www.databaseanswers.org/data_...ales/index.htm
When reading your original question I couldn't help but wonder if your model wasn't ideal, but I don't know the specifics of your project.

Looping over a recordset like this:

Do while Not rst.EOF
  If rst![MyField] <> Something Then  'The real loop exit condition.
    Exit Do
  End If
  ' Rest of your code here.

  rst.MoveNext
Loop

Review these snippits http://allenbrowne.com/ser-29.html#L...thout_MoveNext

[kd2017]

Here is what your code might look like to loop through a subform's recordset:

Private Sub Command15_Click()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qry As String
    Dim total_stock as Double
    Dim allocated_stock as double

    Set db = CurrentDb
    Set rst = Me.OrderProduct.Form.RecordsetClone
    If Not (rst.BOF And rst.EOF) Then
        rst.MoveFirst
        Do While Not rst.EOF
            '----------------------------------
            'BEGIN CODE TO EXECUTE UPDATE QUERY
            
            total_stock = 1 'change these to how ever you're calculating these fields. refere to fields in the subform's recordset like rst![field_name]
            allocated_stock = 1

            qry = "UPDATE ProductTbl SET TotalStock = " & total_stock & ", AllocatedStock = " & allocated_stock & " WHERE ProductId = " & rst!ProductId
            db.Execute qry, dbFailOnError
            
            'END CODE TO EXECUTE UPDATE QUERY
            '----------------------------------
            rst.MoveNext
        Loop
        
    End If
    rst.Close
    
ExitHandler:
    Set rst = Nothing
    Set db = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

[Micron]

I expect that if you loop over the form recordset clone and there is any unsaved records, that will be a problem. Might be simpler to use AfterUpdate event of the quantity control on the form to update the IN and OUT fields for the current record?

[kd2017]

^-- what he said. Or maybe the afterupdate event on the subform rather than the specific control?

But they did mention something in the first post about a confirmation button.

[LGrossman]

That's really helpful thanks!!

[Micron]

You want the ID of the record so that you know which record to update, which you can't get if you move off of the record. AFAIK, the form only updates after you move off of the record (barring closing of the form). Clicking any button will certainly cause you to move off of the record. You'd get the id of the current record during a control event.

[kd2017]

When a form saves a record I *think* it'll execute a beforeupdate event and then an afterupdate event and then change records, so the updating id should be available to the afterupdate event.

[Micron]

I think you are correct, so I guess either will work if written properly. The potential problem I see in using the form event is that it will fire no matter which field is updated when moving off the record. So 'frinstance, if you're creating records except for certain field values (e.g. intending to add quantities when all records have been entered), you have no qty field values and can end up raising calculation errors (e.g. division by zero). As you probably know, give users a bullet proof db and someone will find a way to break it. IMO the only way to ensure the calculation is done only when the field has a value is to use the control event, and even then, only after validating the entry. Having said that, I would probably use the control BeforeUpdate event as there's not much point in validating data after it has been allowed in.

[LGrossman]

Here is what your code might look like to loop through a subform's recordset:

Private Sub Command15_Click()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qry As String
    Dim total_stock as Double
    Dim allocated_stock as double

    Set db = CurrentDb
    Set rst = Me.OrderProduct.Form.RecordsetClone
    If Not (rst.BOF And rst.EOF) Then
        rst.MoveFirst
        Do While Not rst.EOF
            '----------------------------------
            'BEGIN CODE TO EXECUTE UPDATE QUERY
            
            total_stock = 1 'change these to how ever you're calculating these fields. refere to fields in the subform's recordset like rst![field_name]
            allocated_stock = 1

            qry = "UPDATE ProductTbl SET TotalStock = " & total_stock & ", AllocatedStock = " & allocated_stock & " WHERE ProductId = " & rst!ProductId
            db.Execute qry, dbFailOnError
            
            'END CODE TO EXECUTE UPDATE QUERY
            '----------------------------------
            rst.MoveNext
        Loop
        
    End If
    rst.Close
    
ExitHandler:
    Set rst = Nothing
    Set db = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

Just wanted to say Thank you all for your help. With some minor changes to the code above to fit my project, it works really well (although I'm sure it could be more tidy). This is what I did:-

Private Sub OrderCompleteBtn_Click()

Dim Answer As String

If Nz(Me.EnquiryID) = 0 Then
MsgBox (" No Order Selected! ")
Else

Answer = MsgBox(" Are you SURE you want to complete the Order? ", vbYesNo + vbQuestion)

If Answer = vbYes Then

If Me.Dirty Then
Me.Dirty = False
End If

On Error GoTo ErrHandler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qry As String
Dim PTotal_Stock As String
Dim PAllocated_Stock As String
Dim FTotal_Stock As String
Dim Total_Stock As String
Dim Allocated_Stock As String

Set db = CurrentDb
Set rst = Me.[OrderItemTbl subform].Form.RecordsetClone
If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
Do While Not rst.EOF
'----------------------------------
'BEGIN CODE TO EXECUTE UPDATE QUERY


PTotal_Stock = DLookup("[TotalStockQty]", "[ProductTbl]", "ProductId = " & rst!ProductID)
PAllocated_Stock = DLookup("[AllocatedQty]", "[ProductTbl]", "ProductId = " & rst!ProductID)

FTotal_Stock = [Forms]![OrderEnquiryFrm]![OrderItemTbl subform].[Form]!QtyTxt
Total_Stock = Val(PTotal_Stock) - Val(FTotal_Stock)
Allocated_Stock = Val(PAllocated_Stock) - Val(FTotal_Stock)

qry = "UPDATE ProductTbl SET TotalStockQty = " & Total_Stock & ", AllocatedQty = " & Allocated_Stock & " WHERE ProductId = " & rst!ProductID

db.Execute qry, dbFailOnError

'END CODE TO EXECUTE UPDATE QUERY
'----------------------------------
rst.MoveNext
Loop

End If
rst.Close

Me.ConfirmedChk = True
MsgBox (" Order is Complete! ")
Else
Exit Sub
End If
End If

ExitHandler:
Set rst = Nothing
Set db = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, , "Error #" & Err.Number
Resume ExitHandler
End Sub