lock values on change?

[Guest]

Part of my database tracks follow up letters sent to physicians. On my form
I have 3 textboxes that accept a date for the 1st, 2nd, and 3rd letter and a
textbox that is filled with a number id for the pending status of the letter
( these numbers come from a lookup table, 1 = pending, 2 = 1st letter sent, 3
= 2nd letter sent, etc.). When the user enters a date for each letter, the
pending status is set to change automatically in each letter's after update
event. The problem comes when a user goes to make a correction to dates
previously entered. For example, if the 1st and 2nd letter dates have been
recorded and the pending status has been set to "2nd letter sent"
accordingly and the user then goes to make a correction to the 1st letter
date, the pending status reverts back to say "1st letter sent" which isn't
the case. Is there a way I can lock the value of that pending status to
prevent it going backwards when the user makes changes to older dates entered?
Thanks, Julie

 

[Guest]

Julie:

You could make the updating of the Pending Status in the AfterUpdate event
procedure of each control conditional on the value being no greater than the
current value, e.g. for letter one:

If Me.[Pending Status] <= 2 Then
Me.[Pending Status] = 2
End If

BTW storing the letter dates in three separate columns in the table is not
an ideal design (its what's known in the jargon as 'encoding data as column
headings' whereas the relational model requires that data should only be
stored as values at column positions in rows in tables). Better would be to
store them as separate rows in a related table with a LetterDate and a
LetterNumber column. The dates could be entered via a subform in the main
form.

Ken Sheridan
Stafford, England

 

[Guest]

Yeah, I know I need more normalization. This was my first attempt at access
databases so I have a few tweaks to make for version 2. Nothing like
diving in the pool to learn to swim. Thanks for the help.

Julie:

You could make the updating of the Pending Status in the AfterUpdate event
procedure of each control conditional on the value being no greater than the
current value, e.g. for letter one:

If Me.[Pending Status] <= 2 Then
Me.[Pending Status] = 2
End If

BTW storing the letter dates in three separate columns in the table is not
an ideal design (its what's known in the jargon as 'encoding data as column
headings' whereas the relational model requires that data should only be
stored as values at column positions in rows in tables). Better would be to
store them as separate rows in a related table with a LetterDate and a
LetterNumber column. The dates could be entered via a subform in the main
form.

Ken Sheridan
Stafford, England

Part of my database tracks follow up letters sent to physicians. On my form
I have 3 textboxes that accept a date for the 1st, 2nd, and 3rd letter and a
textbox that is filled with a number id for the pending status of the letter
( these numbers come from a lookup table, 1 = pending, 2 = 1st letter sent, 3
= 2nd letter sent, etc.). When the user enters a date for each letter, the
pending status is set to change automatically in each letter's after update
event. The problem comes when a user goes to make a correction to dates
previously entered. For example, if the 1st and 2nd letter dates have been
recorded and the pending status has been set to "2nd letter sent"
accordingly and the user then goes to make a correction to the 1st letter
date, the pending status reverts back to say "1st letter sent" which isn't
the case. Is there a way I can lock the value of that pending status to
prevent it going backwards when the user makes changes to older dates entered?
Thanks, Julie