Using Calculated Controls (1 Viewer)

MartinC

New member
Local time
, 17:00
Joined
Nov 9, 2012
Messages
3
I have a farming database. One of the tables contains the amount of chemicals a farmer has on hand. I have a form that lists the chemicals available to use on different fields. The form has a field that allows the farmer to enter the amount of chemicals used on each field. How would I use a calculated field to update the table and then show the farmer (on the same form) how much of each chemical he has left? :confused:
The farmer leases fields from different people and uses different chemicals depending on which field he's working and the crop he has planted.
Any help would be greatly appreciated. Thanks.
Martin
 

Isskint

Slowly Developing
Local time
Today, 01:00
Joined
Apr 25, 2012
Messages
1,302
Well the first thing to consider is NOT storing a calculated value in a table. If you record receipts in 1 table and how much is used in another table, you can normally calculate the value each time you need it. Have a look at this article for some advice http://allenbrowne.com/casu-14.html

Essentially you want a formula that totals all the chemicals bought/given to that farmer and deduct the current stock level of each chemical owned by that farmer. so sum((chemicals received) - (chemicals used)). That said you would probably need something that like;

DSum("ChemicalNameField1","ChemicalReceivedTable","((ChemicalNameField1= '" & ChemicalNameField1 & "') AND (FarmerName='" & FarmerName & "'))") - DSum("ChemicalNameField1","ChemicalUsageTable","((ChemicalNameField1= '" & ChemicalNameField1 & "') AND (FarmerName='" & FarmerName & "'))") ...etc field2...field3

If we go with a table that stores the values on hand as they are adjusted
DSum("ChemicalNameField1","ChemicalOnHandTable","((ChemicalNameField1= '" & ChemicalNameField1 & "') AND (FarmerName='" & FarmerName & "'))")...etc Field2...Field2
 

Users who are viewing this thread

Top Bottom