Calculated Field on the Form

[Tamrak]

I have a form, which required a value to be calculated (or lookup) from a table.

Table TotalScores:

Fields: ItemNo (autonumber), Descriptions (text), Score (number)

Sample Data: 1, Breakfast, 25
2, Lunch, .35
3, Dinner, .45
…………..
(Real data has several records.)

On the form, named Budget, there is calculated field called: Payment

In the present, I hard-coded the value of .25 or .35 or .45 into this "payment" field, in order to calculate the amount. The boss would like to be able to change the value in the table at anytime. For example, value can change from .25 to .30 or .35 to .42. So, he would like to use the value entered dynamically from the table instead.

What should I do to link the value from this table to my calculated field? Since there are many descriptions related, how can I pinpoint which one I can choose? Please advise. Are there any formula or something to calculate (lookup) in the control source? Thank you.

 

[Remou]

Have a look at dlookup.

http://www.tek-tips.com/faqs.cfm?fid=4978

 

[Tamrak]

Thank you.

This seems to work now.

I used the following formula:

=([QuantityInput])*DLookUp("[Score]","tblTotalScores","[Descriptions] = 'Dinner'")

This is for that field. When I change the score in the table, the field in the form is automatically followed what I changed the value in the table.