Hi, folks. While I can update fields through a subform one record at a
time, there are times when it would be convenient to be able to update all of
the subform records at the same time.
In the Northwinds database, there is a field called "Discount" in the Order
Details table, and in the Order Details Extended query and the Orders
Subform. The user can enter a discount percentage on each line item in the
order. I have added a similar "Discount" field in the Orders table, and to
the Orders Qry and the main "Orders" form. I'd like to be able to enter a
value in the Orders Discount field and have Access enter that discount to
every item in that order.
I've done a little poking around, and come up with the following code, based
on suggestions from many of the people in this forum. But there's something
that I'm not understanding, because it doesn't QUITE work yet.
------------------------------------
Private Sub Discount_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.Orders_Subform.Form.RecordsetClone
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do Until .EOF
Forms!Orders.[orders Subform].Form.Discount = Me.Discount
.MoveNext
Loop
End With
End If
rst.Close
End Sub
---------------------------------------------------
This code works, somewhat, but only updates the order Details.Discount field
for ONE record, not for all the records displayed in the subform. At first,
I thought it was only updating the first record from the subform, but I have
come to realize that if I select some other record by clicking on it and then
update the Order Discount, that the program updates that specific record and
no others.
The goal is to update all of the Order Details records at once, by copying
the Order Discount percentage to each Order Details Discount percentage.
I had thought that the Do Until .EOF .... Loop structure would cause
Access to loop through all of the detail records and update each one, but
apparently not.
Does anyone see where I've made the error? Thanks.
|