Calculating from a table rather than a query!!! 1

[Airbiskit]

Hi,

I have a form that has a subform within it that is linked another table. This table displays all the training courses taken by our staff. Three important fields in this table are the Attended date, Valid for(years) and the Expiry date. Is it possible to calculate the expiry date from the attended date + Valid for (years) within a table instead of creating a query? What I want this subform to be used for also is so that records can still be entered in when a course has been taken and work out the expiry date automatically.

Any help would be appreciated.

Thanks

Steve

 

[ZmrAbdulla]

Hi,
If you can bring the result on the form,A Macro can do the trick

create a text box to show the result
Name it "Result"
visible = No
ControlSource= attended date + Valid for

Create a Macro
Action = "SetValue"
Set Item = [Forms]![MyForm]![ExpiryDate]
Set Expression = [Forms]![MyForm]![Result]
Name it Macro1

"AttendedDate"
AfterUpdate = Macro1

"ValidFor"
AfterUpdate = Macro1

This will write the Calculated value to the table's field "ExpiryDate"

Hope this will help
Regards

Zameer Abdulla

http://www.mtkhoory.com

http://www.aikah.com

 

[JerryDennison]

You should not store the calculated value in the table. Not only does this violate the Normal Forms, it's completely unnecessary. You may display the Expiration Date anytime you wish (without using a query, although I would recommend using a query. Why do you have a problem with that?).

In an unbound control on your form or report use the following syntax in the controlsource property:

=DateAdd("yyyy", [ValidFieldName], [AttendedDateFieldName])

"Advice is a dangerous gift, even from the wise to the wise, for all course may run ill." J.R.R. Tolkien