lock values on change?

C

cytogirl

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
 
K

Ken Sheridan

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
 
C

cytogirl

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. :)
Ken Sheridan said:
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

cytogirl said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top