Solved

How to Perform a Recalculation in a text box on Form

Posted on 2010-09-23
11
255 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 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

12 Experts available now in Live!

Get 1:1 Help Now