A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to store calculated fields ?



 
 
Thread Tools Display Modes
  #11  
Old June 4th, 2004, 05:06 PM
Kevin Sprinkel
external usenet poster
 
Posts: n/a
Default How to store calculated fields ?

Don't worry about asking for help; this forum has helped
me and countless others get their projects off the
ground. Returning the favor is my pleasure.

If you were simply *displaying* a calculation in a form
control, you would set the Control Source property to an
expression, such as the following for an [ExtdPrice]
control:

= [Quantity]*[UnitPrice]

This expression will be evaluated, but not stored
anywhere.

Since you wish to store your calculation, you will instead
bind the control to the corresponding table field, and set
the value of control (and hence the underlying field)
through VBA. Since the calculations depend on the value
of more than one other control, this is most conveniently
done in the Form BeforeUpdate or AfterUpdate Event
Procedure as described earlier.

So, in your case, the Control Source will not be a
calculation as above, but instead the name of the field in
which you wish to store the value.

HTH
Kevin Sprinkel


-----Original Message-----
Me again
One last question...I promise !

I don't really get which property controls the value of

the bound field I want to be calculated. After the update
of the 'Source Control', I should run a 'Sub' that would
change the value of the calculated field. If its name
is 'calculatedField' bound to a 'Field1' in the table and
set in the 'ControlSource' property, would something
like ' [calculatedField]=IIF(logical test;value if
true;value if wrong) ' or ' [calculatedField].Value=IIF
(logical test;value if true;value if wrong) ' work ?

I guess this is my last question !
thanks for keeping helping newbie or not !
.

  #12  
Old June 4th, 2004, 05:14 PM
Kevin Sprinkel
external usenet poster
 
Posts: n/a
Default How to store calculated fields ?

Your Form AfterUpdate event procedure will look something
like this. I've assumed that your textbox controls are
named txtfieldname as is a common practice.

Sub Form_AfterUpdate()
Dim Discount As Single

' Selects different cases based on value of a single
' control. Syntax is Select Case expression.
' See VBA Help. Press Ctrl-G from form design mode
' and choose Help from VBA window.

Select Case Me![txtQty]
Case 20
Discount = 0
Case 50
Discount = .15
Case 100
Discount = .25
Case Else
Discount = .30
End Select

' Assigns the result of a calculation to another form
' control. Since this control is bound to the field
' "CalcFld1", the value will be stored in the table.

Me![txtCalcFld1]=Me![txtQty]*Me![txtPrice]*(1-Discount)

' Assign other controls here...

End Sub

HTH
Kevin Sprinkel

-----Original Message-----
Me again
One last question...I promise !

I don't really get which property controls the value of

the bound field I want to be calculated. After the update
of the 'Source Control', I should run a 'Sub' that would
change the value of the calculated field. If its name
is 'calculatedField' bound to a 'Field1' in the table and
set in the 'ControlSource' property, would something
like ' [calculatedField]=IIF(logical test;value if
true;value if wrong) ' or ' [calculatedField].Value=IIF
(logical test;value if true;value if wrong) ' work ?

I guess this is my last question !
thanks for keeping helping newbie or not !
.

  #13  
Old June 7th, 2004, 10:41 AM
Nico
external usenet poster
 
Posts: n/a
Default How to store calculated fields ?

Thanks for your help !!

I did it !!


See ya around !
  #14  
Old June 7th, 2004, 02:57 PM
Kevin Sprinkel
external usenet poster
 
Posts: n/a
Default How to store calculated fields ?

Congratuations. My pleasure.


-----Original Message-----
Thanks for your help !!

I did it !!


See ya around !
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.