Results 1 to 3 of 3
  1. #1
    rd.prasanna is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    25

    How to calculate and Update Field via VBA

    Hi All,

    I have a unbound text box which calculate the "Earned Value" based on the other fields of the other tables.
    My concern is how to do it using VBA coding... and which should get update every time user changes the value.


    I tried with change event which is not working... actually i don't know much about Access VBA.

    And can anybody suggest me a best tutorial for Access.

    kindly help me in this regard.
    Thanks in Advance...

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Usually, any field which is a direct calculation of other fields should not be stored as a value, it is just calculated in a query.
    Queries are dynamic, so any time one of the values changes, the calculated value would automatically change.
    Is there some special reason why it cannot be done this way?

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    The OnChange event fires every time a character is entered in a Textbox, which is not really appropriate for this kind of thing; the determining Control's AfterUpdate event is normally used, when it's done at the Form-level.

    JoeM's suggestion of doing it in a Query is a good one, or you could do it using the Control Source Property of the Control where the determining data is being entered. But you need to explain a little more about the specifics, here, especially the "based on the other fields of the other tables" part. How many 'other fields' in how many 'other tables?' The answers to these questions may determine the best approach in this specific case.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculate Date Field
    By RachelBedi in forum Access
    Replies: 3
    Last Post: 01-09-2013, 04:14 PM
  2. Calculate field after combo update
    By TinaCa in forum Forms
    Replies: 3
    Last Post: 06-06-2012, 12:16 AM
  3. Calculate a field in a form
    By alscompany in forum Forms
    Replies: 4
    Last Post: 02-28-2012, 03:09 PM
  4. Replies: 5
    Last Post: 12-17-2010, 11:06 AM
  5. how to calculate Percent in an update query?
    By newtoAccess in forum Queries
    Replies: 4
    Last Post: 11-23-2010, 10:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums