Updating calculated dates from a form to a table

[Shardool]

Hey guys,
Your expert comments have helped me a lot before. So a huge thanks first

Now the problem

I have a table which is used to store info regarding medicines dispensed. I also have a corresponding Form to enter data. The fields in Table are
ID- number
Dispensed Date- date with dd/mm/yyyy format
Dispensed Type- text
Quantity- no. of days
Next Collection Date- date with dd/mm/yyyy format

The Form also contains same fields but it has a calculated field for (
Next Collection Date) where i calculate date using Dateadd function. Also the form has a Datasheet view. So records are added when I press Tab or Enter at last field.

now the problem is the calculated dates arent getting updated in the table. And this is a huge problem as i have to run a query later where i will put a criteria on Next Collection Date.

So plzz help guys...

 

[John Big Booty]

Storing calculated fields, in all but a small number of special cases is generally considered to be in breach of the rules of Data Normalisation.

Generally if you can calculate the value at the time of input you can use the same method to re-calculate that value at any time you need to display it on either a form or report. That way if one of the underlying values is changed for some reason, you don't then have to worry about updating the stored calculated result.

 

[Shardool]

So in other words there is no way to do it... Right... So what do u suggest??? Calculate it using a query???

 

[John Big Booty]

Oh there are ways to do what you want but the question you need to ask is; Should I do it?

The answer in most cases is do your calculation at either form or report level, using an unbound text box with the calculation as it's Record Source, or as you suggest at query level. The method you use will be largely up to you but your data, and nature of the calculation may suggest one method in preference to another.