• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

How to Perform a Recalculation in a text box on Form

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
kmartinenko
Asked:
kmartinenko
  • 4
  • 3
  • 2
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You can use
Me.ReCalc

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

mx
0
 
rockiroadsCommented:
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
 
stupsnoseCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Seems to me that you are not used to programming in Access/VBA"
"SelectionChange "

No such Event :-)

mx
0
 
stupsnoseCommented:
You are right. Try "Click" or "Change"
0
 
kmartinenkoAuthor Commented:
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
 
kmartinenkoAuthor Commented:
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
 
rockiroadsCommented:
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
 
kmartinenkoAuthor Commented:
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
 
kmartinenkoAuthor Commented:
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
 
rockiroadsCommented:
No worries, glad to have helped.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now