How to create multi value select in access form and add values in database table

[afia]

It is the continuation of my post and I have provided all the details above .MajP and GaP42 has provided advise on cascading forms but i am struggling a bit on it. The subcategory is not linking up with the Category. For example, If i select Injury category Hand, Subcategory should only show up categories related to hand and not others. Currently its showing up all the sub categories. It might be a code or something

Thanks

 

[Gasman]

You have not used any criteria, that is why.

 

[afia]

. Criteria as in the Form properties?

 

[Gasman]

. Criteria as in the Form properties?

If you want to filter the data in the combo for something, that criteria needs to be in it's source.
The form and the combo are two separate objects.

 

[afia]

thank you for explaining. I will try it out but i think i am a but clueless on this.

 

[Gasman]

I honestly am struggling making head and tail of your relationships, but you need to use the criteria for injuryID for the respective field in subcategory.

 

[mike60smart]

thank you for explaining. I will try it out but i think i am a but clueless on this.

Hi
See the example attached.

 

[afia]

Hi
See the example attached.

Thank you so much for explaining in detail and helping.

 

[GaP42]

@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.