Discussion Locked

Access 97 Set Value

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have an Access 97 database. I have several forms created. One set of fields on the forms relates to Board Members. I would like to set up a macro that will allow the Board Member to fill in his/her term start date and upon exiting the field Access will autofill in the term end date, which would be 3 years out.

Can this be done? Any help would be greatly appreciated.

7 replies

by dklippert

Insert an unbound text box and for the Data>Control Source use a formula like:
=[StartDate]+1095
This won't catch leap years, but it's a start.

by lisa.yewers

This could be accomplished by using the DateAdd function to add three years to the start date (no issues with leap year with this method).

Assuming the table your form is based on has a StartDate field and TermDate field, the following code, when entered as an event procedure in the On Update event property of the StartDate control, will update the TermDate field when the date is entered/edited in the StartDate field:
Me![termdate] = DateAdd("yyyy", 3, [startdate])
You need to ensure thatyou have the two fields on your form and the control name is the same as the field name (control source).

A further advantage of this method is that the TermDate field would be updated immediately on screen for the user to see without having to close and re-open the form.

by bob sellman

The second answer is basically correct except that the function that is executed on the OnUpdate property of the control me.Start should refer to the controls, not attempt to use and update values from the table. Therefore, the function should readas follows, assuming the starting date control is called Start and the end of the term control on the form is called Term:

me.term = DateAdd("yyyy", 3, me.start)

Note that the record source for me.start should be the StartDate column in the data table and the record source for me.term should be the TermEndDate column in the data table.

This discussion is closed to new replies.