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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
How to store calculated fields ?
Thanks for your help !!
I did it !! See ya around ! |
#14
|
|||
|
|||
How to store calculated fields ?
Congratuations. My pleasure.
-----Original Message----- Thanks for your help !! I did it !! See ya around ! . |
|
Thread Tools | |
Display Modes | |
|
|