unbound field value carries to next new record

[pd06498]

I have a database and a form into which I input data. I have an unbound field which is a calculated future date base on the startdate field and the tenure field (in other words I need a date 2,3 or more years [determined by the tenure field] on from the startdate.

Now I have the formula (using DateAdd) to get the correct result. But, when I go to add a new record, the figure in the unbound field is displayed in the new record until a new startdate is selected.

I have one part of the formula in the on update of the startdate, and the same formula in the forms on current. I think I might need to use an If-Then code, but cannot remember how to do that.

Any help appreciated, as I have been snooping around this and other forums for the netter part of the last week looking for something that might help.

 

[THWatson]

pd06498
A suggestion to try.

In the Current event for your form, put this code...
If Me.NewRecord Then
Me.TenureDate = Null
Else: TenureDate = DateAdd("yyyy", 3, [StartDate])
End If

That will reset the TenureDate when a new record appears.

Then in the AfterUpdate event for your StartDate field, put
TenureDate = DateAdd("yyyy", 3, [StartDate])

See if that works.

Tom

 

[THWatson]

pd06498
You will notice, of course, that the code I suggested doesn't take into account the variable nature of the Tenure date (you mention 2, 3 or more years, and I have shown a method only for 3 years).

But maybe this will give you a direction in which to go.

Tom

 

[pd06498]

Tom

Works like a dream. Thanks for the direction.