×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Subform not advancing to next record

Subform not advancing to next record

Subform not advancing to next record

(OP)
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.

CODE -->

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

CODE -->

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.

RE: Subform not advancing to next record

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.

RE: Subform not advancing to next record

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close