bng0005
asked on
Detecting subform recordset change
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]![txtShippi
Forms![frmMain]![txtTotal]
Bryan
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]![txtShippi
Forms![frmMain]![txtTotal]
Bryan
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Great!
J
J
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