Checkboxes, calculated fields and other fun stuff

[Bellyjeans]

Hi all of you wonderful Access experts,

I'm absolutely baffled. I'm keeping track of the end dates of
employees' probationary periods, however, if an employee goes on
leave, their probationary period automatically extends by one year.
On my form, I have a text box that where we can enter the probationary
end date called txtProbationaryEndDate, as well as a combo box called
cboProbationaryPeriodExtended, and another combo box called
cboReasonForProbationaryPeriodExtension.

I need to make it so that if "Yes" is selected in
cboProbationaryPeriodExtended, one year is automatically added to
txtProbationaryEndDate. This needs to be stored in the field that's
bound to txtProbationaryEndDate. I have a number of questions: Is
this a violation of data normalization rules? If so, what's the best
way to go about handling this because the value of the
ProbationaryEndDate field needs to be automatically changed an
employee goes on leave. If it can be done, what kind of coding has to
be in place to do this?

Many thanks.

 

[Allen Browne]

Storing the ProbationaryEndDate is not a violoation of normalization if
there could be valid reasons why the value may be different than the
calculated result. For example, if a court might ever assign a different end
date (e.g. shortening a sentence, or canceling a probation), you have a good
reason for storing the actual value. More info about that argument here:
http://allenbrowne.com/casu-14.html

If you wish to automatically add 12 months to your stored date field, you
could use its AfterUpdate event procedure of the check box to do that. But
there are a couple of other things you need to consider as well. What if the
user clicks the box off and on a couple of times? Does that add multiple
years? Or do you need to subtrack 12 months whenever the user unchecks the
box?

Sample code:

Private Sub Check1_AfterUpdate
Dim iYears As Integer
If Me.Check1.Value Then
iYears = 1
Else
iYears = -1
End If
Me.txtProbationaryEndDate = DateAdd("yyyy", iYears,
Me.txtProbationaryEndDate)
End Sub