Subform not advancing to next record 1

[TrekBiker]

The setup is an Orders form with an Order Details Subform.

The code below applies discounts according to volumes, with the discount % increasing as the cumulative volume increases. It works fine.

Private Sub Form_AfterUpdate()
    Dim Disc As Variant
    Dim TF As Variant
       
   'Calculate the total added firkins every time a new order item is entered
    TF = 0
    With Me.RecordsetClone
        .MoveFirst
            Do Until .EOF
                TF = TF + !Firkins
        .MoveNext
        Loop
    End With
    
   'Set discount percentage based on total firkins in order
    If TF < Me.Parent.VDLevel1 Then Disc = Me.Parent.DP1
    If TF >= Me.Parent.VDLevel1 And TF < Me.Parent.VDLevel2 Then Disc = Me.Parent.DP2
    If TF >= Me.Parent.VDLevel2 And TF < Me.Parent.VDLevel3 Then Disc = Me.Parent.DP3
    If TF >= Me.Parent.VDLevel3 Then Disc = Me.Parent.DP4
   
   'Calculates the discount for each line item and the Net Line Total
    With Me.RecordsetClone
        .MoveFirst
            Do Until .EOF
        .Edit
            If !Firkins > 0 Then !DiscountPercent = Disc
            !Discount = Round(!DiscountPercent * !UnitPrice / 100, 2)
            !NetLineTotal = !Quantity * (!UnitPrice - !Discount)
        .Update
        .MoveNext
        Loop
    End With
    
   'Update the total firkins calculation?
    If Me.Dirty Then Me.Dirty = False
    Me.Parent.TotalFirkinsInPeriod.Requery
                   
End Sub

For the time being the client wants to revert to entering discounts manually so I've reduced the code to this

Private Sub Form_AfterUpdate()

    NetLineTotal = Quantity * (UnitPrice - Discount)

'    Me.Requery
'    If Me.Dirty Then Me.Dirty = False
                   
End Sub

Now it calculates the first item's total but the main form isn't being updated and the focus won't move to the next row to allow a new item to be added. I tried the lines commented out but they gave errors, for example with Me.Requery

Run-time error 2115
The Macro or Function set to the BeforeUpdate or Validation Rule property of this field (hitting Debug highights Requery)is preventing the form from saving the data in this field. There is no BeforeUpdate procedure for this subform.

 

[MajP]

I do not really understand this. Why are you storing netLineTotal? You store quantity, unit price, and discount. Netlinetotal should be a calculated field in a query and not stored.

 

[TrekBiker]

Thanks MajP, you're right. The design is a legacy left by a designer who's long gone and the whole application is riddled with idiosyncrasies. The original AfterUpdate code had worked when it needed to calculate the cumulative volume and reapply discounts line by line. But using the query to calculate NetLineTotal in the simplified version fixes everything.