asked on

Understanding after update event in subform text box

I have an unbounded textbox on a form that calculates the total cost of a construction project at the oncurrent event of that form. Subforms which is embedded on a series of tabcontrol pages are used to input the itemised construction costs.

The form oncurrent event works fine as you move from one form to another. However, I want the total cost to change in real time when I make amendments to the itemised cost. I tried using the following code in the after update event of the subform textbox to do this:

Call Me.Parent.Parent.Form_Current

The problem I am having is that the total cost is not updated when I make a single amendment. When I make a second amendment the total cost changes but only take the first amendment into account.

I reckon that this has got something to do with the order in which events are taking place in the DB but I just can't pin it down. Can someone please help resolve this problem

dbHomeBuildingCostEstimate.mdb

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sheils

ASKER

OK the recalc button does the trick. However, the form goes back to the first record when I click the button. Since it is for personal use that will do for know. But, any advise on this matter will be welcome

dbHomeBuildingCostEstimate.mdb

Jeffrey Coachman

1. What was wrong with the code you had there before?
(Me.Recalc)
?
That seemed to be working just fine?
Why the change to me.requery?

2. You should alway try torequery the individual controls before resorting to requery-ing the entire form (which forces the form back to the first record).

Because all you subforms and controls have the exact same name, this may be difficult, but I changed the name of one of the textboxes and the requery of just that textbox worked fine:

[chdExcavation].Form![chdWorksItemised].Form![txtTotalPreliminaryCost1].Requery
'DoCmd.Requery

In any event, here is the generic code to put you back the the record you were on before the requery.

Dim ProdID As Long
Dim rs As Recordset
ProdID = Me.[Product ID] 'set the variable to the current Record's ID
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "[Product ID]=" & ProdID ' find the record using the variable
Me.Bookmark = rs.Bookmark

Sample that just requeries the Textbox is attached

JeffCoachman

dbHomeBuildingCostEstimate.mdb