Add label to form dependent on data

[D-Angle]

I have a database with 2 main tables that I am building for managing patients. The first table contains the patient's information, and the second table contains the details of each referral. They are linked by a unique id number for each patient, so you can only enter each patient once, but have multiple referrals that are linked to that patient's details.

I have a form with the patient's details, and the details of each referral in a continuous subform. I would like the form to display a label saying 'Discharged' she the patient's most recent referral has a discharge date, and 'Current Patient' when the patient's most recent referral doesn't have the discharge date filled in. I have tried numerous ways of doing this but no luck, is there a way to get the form to display this when you select a patient to view?

 

[CJ_London]

rather than a label, use an unbound textbox where enabled is false and locked is true.

without knowing your field names and what fields you have it is not worthwhile speculating how to populate it, but you would probably use a combination of the dlookup and dmax functions.

 

[D-Angle]

I tried using an unbound textbox in a nother subform which was set to show the most recent referral. Again, no joy.

The patients' info is held in TBL_Patients and the referral infomation is held in TBL_Referrals, and it's the most recent entry in the field Discharge_Date in TBL_Referrals that needs to be blank to return the value 'Current Patient', or filled with a date to return 'Discharged'.

 

[CJ_London]

bearing in mind that databases have no sense of order unless you provide one, define 'most recent'.

Also, provide the details of your tblReferrals - field names, datatypes, example data