Solved

Detecting subform recordset change

Posted on 2006-06-16
5
496 Views
Last Modified: 2008-03-04
Hey experts,

I have a form for dealing with purchase orders with a subform for the items being ordered. I'm trying to get a field on my main form to be a running total field, so as the user inputs information on the subform, the field will update with the total cost of the order.

I have it right now where it updates to the correct total when the shipping or tax information changes on the main form, but I can't figure out how to get the subform changes to update correctly

There are only 2 fields on the subform I want the check for changes, quantity or price. Right now, both have code to update the Amount field of the subform on a change, so I could probably just check for a change on the amount field, but can't get it to work.

I tried the following code and it would update about half the time, but it was always the incorrect value. Any help is appreciated. Thanks.

    Dim dPrice As Double
    Dim rs As Recordset
    dPrice = 0
   
    Set rs = Me.RecordsetClone
    rs.MoveFirst
    While Not rs.EOF
        dPrice = dPrice + rs.Fields("Amount").Value
        rs.MoveNext
    Wend
   
    dPrice = dPrice + Forms![frmMain]![txtShipping].Value + Forms![frmMain]![txtTax].Value
    Forms![frmMain]![txtTotal].Value = dPrice


Bryan
0
Comment
Question by:bng0005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16922599
Creating a clone before the record is saved works?

First off, you can't do this...."quantity or price. Right now, both have code to update the Amount field of the subform on a change"
Both have to be > 0 otherwise you're charging for 0 quantity (backorder) or you're selling quantity without cost. If these are ok...then continue...if not, then you need to create a message to inform the user that he/she can't break the rules. Do the Totals update only on the before update event of the price field. so

Validate quantity before totaling price
Quantity > 0
Total price = quantity X price

Add total prices together as lines are added like you are.
Try adding a forms!frmMain.requery or Refresh at the end.
J

0
 
LVL 1

Author Comment

by:bng0005
ID: 16923015
One question, how would you handle the user going back and updating the quantity field after the price has been entered, i.e. I'm entering in the information, after I enter in price, Amount gets updated, but then I see I entered in the wrong quantity, but an update to this field only will not fire the code to update total price if I understand your suggestion correctly.
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
ID: 16923080
try using the subform's before_update event, that way the calculation works when you leave the record, instead of leaving the field. That way you can validate your quantity and cost > 0 at the same time.
J
0
 
LVL 1

Author Comment

by:bng0005
ID: 16923286
ok, got it working. I put the validation code in the beforeUpdate event of the form, and the code to update my running total in the afterUpdate of the form, was the only way I could get around a 2115 error code, but it's now working like it should. Thanks for the help.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16923523
Great!
J
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question