@afia there are few problems with your sample db:
- the relationships between tblPatientInjury and tblInjurySubCategory and tblNatureofInjury are incorrectly formed.
- the primary key fields should not be text, and have an inherent meaning (ie be used to infer a classification) - they are autonumber so you do not have to enter a value and ensure they are unique).
@mike60smart corrected the primary key issue and modified the relationships - however the relationships are still not correct:
- speaking conceptually - the patient has an injury which is described specifically by its association to the item in tblInjurySubcategory. Every item in tblInjurySubCategory is classified as a type of Nature of Injury. So the relationship is tblNatureofInjury has 1:many relationship to tblInjurySubCategory. A tblInjurysubcategory may be used to describe multiple injury records in tblPatientInjury.
With that established/corrected I have used Mike's version, and added a query (qryNoISubCat) to populate the second combo of the subform, filtered according to the selected Nature of Injury. That then shows and allows the addition of new records in tblPatientInjury.
I also added an index on ReferralID and InjurySubCat_FK as unique to prevent duplicate entry of the same kind of injury for the same referral.
You will need form/s for maintenance of NatureofInjury and InjurySubCategory
I hope this is in alignment with what you require.