Sequential date fields - upon entering a date will force drop down selection

[Saabra]

Hi,
I'm not the best at writing code, so whatever solution you provide will need to be dumbed-down.

I'm in design phase of a small database that is capturing referrals to a hospital. There will be five date fields that need to follow a sequential order.

1. Referral Recieved
2. Referral in the cue for admission
3a. Referral admitted to hospital or
3b. Referral not admitted after being in the cue or
3c. Referral denied

There will be a "Referral Status" field, a drop-down, with the following choices:

CONSIDERED FOR ADMISSION
ADMITTED
IN THE CUE BUT NOT ADMITTED
DENIED

When 2 is selected, I want the referral status field to select "CONSIDERED FOR ADMISSION"
When 3a is selected, I want the field to show "ADMITTED"


When 3b is selected, I want the field to show "IN THE CUE BUT NOT ADMITTED"
When 3c is selected, I want the field to show "DENIED"


How do I have the "Referral Status" field respond to dates entered in the status date fields? Can this be done in a query? Also, how can I enforce the sequential rule of the date fields? Lastly, how do I have the referral record disappear from the user's view if ADMITTED, IN THE CUE BUT NOT ADMITTED, or DENIED dates are entered?

Thank you in advance for your help on this.
Saabra

[June7]

Build a form bound to table and write code. Conditional Formatting can also help - text and combo boxes can be set Enabled/Disabled depending on whether another control has data.

Status does not have to be a field in table. This can be calculated when needed. An expression in query or textbox like:

=Switch(Not IsNull([Denied]),"Denied", Not IsNull([NotAdmittedAfter]),"IN THE CUE BUT NOT ADMITTED", Not IsNull([Admitted]), "ADMITTED", Not IsNull([InCue],"CONSIDERED FOR ADMISSION")

Or write a VBA custom function that can be called from query or textbox.

Records 'disappear' by applying filter criteria and refreshing the form. Form RecordSource could be a query like:

SELECT * FROM tablename WHERE Not ADMITTED Is Null AND Not [IN THE CUE BUT NOT ADMITTED] Is Null AND Not DENIED Is Null;

Code in control's AfterUpdate event would requery the form: Me.Requery

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Nor ALLCAPS. CamelCase is easier to read.

[Saabra]

June7,
I'll see what I can understand and do with your recommendation. Thanks so much for your help.
Saabra