Understanding after update event in subform text box

Posted on 2009-04-17
Medium Priority
Last Modified: 2013-11-28
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

Question by:Sheils
  • 2
LVL 74

Accepted Solution

Jeffrey Coachman earned 1500 total points
ID: 24173897
There is absolutely nothing wrong with using a "recalc" button like you are using.

If fact, using a "Refresh" button is the way most developers might handle a situation like this.

Another alternative is to trigger a recalc of the Total textbox on every AfterUpdate event of every textbox.

Let's see if other Experts can provide more input...


LVL 16

Author Comment

ID: 24177833
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

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24178057
1. What was wrong with the code you had there before?
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:


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
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



Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

864 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