Solved

How to Perform a Recalculation in a text box on Form

Posted on 2010-09-23
11
258 Views
Last Modified: 2012-08-14
Hi All,

I have a form in Access 2007 (2002-2003 file format).  On this form I have 1 combo box and two text boxes whose record sources are linked to a main table.

The functionality of this set up is this:

combo1: [YeartoBeBuilt_combo]  txt1: [Cost_txtbox]  text2: [YearofExpenditureCost_txtbox]

What happens is when a user makes a Year selection in the combobox (year to be built), a calculation is peformed based on the year selected and the value entered for txt1(Cost).  This calculation is returned to text2 (YearofExpenditureCost) and subsequently, the recordsource in the table is updated automatically.  

I perform this procedure currently as an AfterUpdate() procedure in the YeartoBeBuilt combo box as such:
Private Sub YearToBeBuilt_AfterUpdate()
YearofExpenditureCost = Me.Cost * (1 + 0.031) ^ (Me.YearToBeBuilt - 2010)
End Sub

Open in new window


This works find and dandy but only after the Year to Be Built combo box has been reselected.  In other words, if records in the form already have a year selected, the Calculation will not show up in the YearofExpenditureCost text box field.   How do I tell the the form to refresh the calculated field so that one does not have to manually re-select the year to be built value?

Many thanks for the help!
0
Comment
Question by:kmartinenko
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 75
ID: 33747532
You can use
Me.ReCalc

... not sure where you need to put this however...

mx
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33747724
see if this works
create a procedure in your form

private sub ReCalcit
    YearofExpenditureCost = Me.Cost * (1 + 0.031) ^ (Me.YearToBeBuilt - 2010)
end sub

then call it on form_current and on afterupdate and whenever you need to
0
 
LVL 1

Expert Comment

by:stupsnose
ID: 33747775
have a look for other events on that combo. I think SelectionChange is the one you are looking for.

(Seems to me that you are not used to programming in Access/VBA. If you place your cursor in the function you mentioned above, in the upper right corner of that code-window you have a combobox saying "AfterUpdate". Select "SelectionChanged" in that combo and press enter. You get a new Function that is called after one selects a new value in you combobox.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 75
ID: 33747796
"Seems to me that you are not used to programming in Access/VBA"
"SelectionChange "

No such Event :-)

mx
0
 
LVL 1

Expert Comment

by:stupsnose
ID: 33747824
You are right. Try "Click" or "Change"
0
 

Author Comment

by:kmartinenko
ID: 33748084
stupsnose--none of your solutions work.  I have tried placing the procedure under Form properties for  Click, Change, Load, OnCurrent, and SelectionChange...none of these automatically "refresh" the YearofExpenditureCost field on the form.  Perhaps I am not being clear enough with what I am asking?  I have 364 records in the form.  Many have year to be built and Cost info already entered.  The AfterUpdate() procedure works properly, but only after the user re-selects the year from the drop down combo box, or creates a new record with the new selection.

What I am asking is if there is a way for the user to toggle through each of those records and have the AfterUpdate procedure execute as to fill in the YearofExpenditureCost automatically?

rockiroads:  I think your proposed solution may yield what I am after, but you are going to need to provide a bit more detail in order for me to comprehend because yes, I am somewhat of a VBA n00b.

Thanks!
0
 

Author Comment

by:kmartinenko
ID: 33748261
To rockiroads:

Ok, so I created a procedure called ReCalcIt()

So now, when I open up Form_Current(), what is the proper way to call it?

Thanks!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33748285
I think by reading your issue is when you load a record and do not select something from the combobox then your YearofExpenditureCost field is not calculated

so inside your vba code for the form you add a procedure. Doing it this way ensures it is reusable at any time within that form


private sub ReCalcit
    YearofExpenditureCost = Me.Cost * (1 + 0.031) ^ (Me.YearToBeBuilt - 2010)
end sub


Now this is the same code as in your combo box afterupdate event right? i.e. your current code
Private Sub YearToBeBuilt_AfterUpdate()
YearofExpenditureCost = Me.Cost * (1 + 0.031) ^ (Me.YearToBeBuilt - 2010)
End Sub


So now instead of calculating it here we call our reusable procedure
Private Sub YearToBeBuilt_AfterUpdate()
    ReCalcit
End Sub

Same affect.

Now if your form is bounded, which I assume it is (linked to a table or query) then the event Current is triggered each time a record is loaded or added. Maybe we can update the field on that as well

private sub form_current
    ReCalcit
end sub


Note now we do not duplicate code. If you ever need to make changes to the formula it is in just one place.

To make it more reusable outside this form we would create a function in a module that takes 2 arguments, cost and year. But thats not an issue right now. Its just FYI.
0
 

Author Closing Comment

by:kmartinenko
ID: 33748322
Thanks rockiroads--I was able to call the procedure you suggested successfully after just using common sense...which at times I seem to lack.  Anyway, many thanks for the help!
0
 

Author Comment

by:kmartinenko
ID: 33748352
rockiroads--thank you very much for the thorough explanation.  You have been awarded all points for your efforts.  I will save this to my knowledge base so I can look it up again--I don't work in VBA/Access a whole lot but recently I inherited this project, so I have to brush up again.  Thanks again!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33748917
No worries, glad to have helped.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

789 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