Understanding after update event in subform text box

Posted on 2009-04-17
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
    LVL 74

    Accepted Solution

    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

    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
    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 Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now