how to add categories and sub categories in subform

[afiasiddiqa]

I have below 4 tables.Table1:ID, OHN (this is primary key), Dob, Type of referral, Reason of referral, date of referral received , clinician

tblInjurySubcategory,tblNatureofInjury,tblPatient_ injury. I have created a Physio Form. I have tried to create subform with category and sub category. It is showing categories/sub categories but when i select for example, Shoulder then it dhoesnt show related sub categories for shoulder. Not sure, what i am doing wrong. Pease can you help

[Welshgasman]

Crossposted first at https://www.access-programmers.co.uk...-table.331124/

[mike60smart]

Hi

There are numerous things wrong with your database.

You should not be using Text data Type as a Primary Key.

Each table should have an Autonumber Long Integer Primary Key.

Your tables should not have fields set as Lookups. Google the Evils of Lookup fields in Access Tables.

Your relationship should be as follows.

Then using MajP's example you should have a Main Form based on the tblReferrals
With a Subform based on tblPatientInjury
In the Subform you would create Cascading Combox's.

1st Combobox looks up the tblNatureOfInjury
2nd Combobox would contain the list of SubCategories

[ArviLaanemets]

Then using MajP's example you should have a Main Form based on the tblReferrals
With a Subform based on tblPatientInjury
In the Subform you would create Cascading Combox's.

1st Combobox looks up the tblNatureOfInjury
2nd Combobox would contain the list of SubCategories

l
Another option is to have 2 unbound combos in main form to select injury category and subcategory, and have the subform linked to those unbound combos too. Or even have the unbound main form with those combos, link the subform to those combos only, and select the patient in subform.

Any new record added into subform will get category and subcategory id's from those unbound combos automatically.

Links to unbound controls can be created only when editing subform properties!

[orange]

In addition to the comments received so far, I recommend no field names with embedded spaces. They will come back to haunt you.

[afiasiddiqa]

Hi

There are numerous things wrong with your database.

You should not be using Text data Type as a Primary Key.

Each table should have an Autonumber Long Integer Primary Key.

Your tables should not have fields set as Lookups. Google the Evils of Lookup fields in Access Tables.

Your relationship should be as follows.

Then using MajP's example you should have a Main Form based on the tblReferrals
With a Subform based on tblPatientInjury
In the Subform you would create Cascading Combox's.

1st Combobox looks up the tblNatureOfInjury
2nd Combobox would contain the list of SubCategories

Thank you so much for the detailed reply and explaining it to me. I have designed the database as per your advise. I had the Main form designed on the basis of Table1 and subform on Tbl PatientInjury .Everything was working fine, its just all subcategories were appearing in combobox when in actual it should show only those selected in Category field. For example , if i select shoulder then only its sub category should appear in combo box.

Now comboboxes are not appearing in the sub form. Not sure why🤦*♀️

Would be able to advise please.

Thank you so much for your help

[mike60smart]

Hi
Can you upload your latest version of the database?

[afiasiddiqa]

I have added the zip file .

Many Thanks

[afiasiddiqa]

and this is the initial database file but this is with alpha numeric primary keys

Many Thanks

[mike60smart]

Hi

The subform needs to be set as a Continuous Form vice Datasheet.

You should name the Forms with the prefix "frm" vice "tbl"

The first Combobox looks up the list of Categories from the tblNatureofInjury.

The second Combobox looks up the list of Subcategories.

Look at the properties of the 2nd Combobox where a reference is made to the 1st Combobox to limit the list.

[afiasiddiqa]

Hi,

Thank you so much. Is InjurySubCat_FK hidden behind SubCat? as when i click on just drop down arrow the it shows the properties with query in Row source.

I will create more combos based on this logic.

Many Thanks for your help

[mike60smart]

The SubCat Combobox is made very narrow to only display the dropdown arrow.
Then in the after update of the Combobox there is an event that populates the Control SubCat

[Welshgasman]

Hi

The subform needs to be set as a Continuous Form vice Datasheet.

Really Mike?
Care to explain why? especially when MajP made this comment in this post https://www.access-programmers.co.uk...4/post-1920218

If you went with Cascading combos to filter the Nature of Injury by an Injury Group it is not trivial to do it in a continuous form. Not super hard but does take some tricks.

[mike60smart]

Hi Welshgasman

MajP did make the comment but in his example he shows how to use Continuous Form.

Is he wrong as well or do you just like nit picking just me?

[Welshgasman]

No, I am genuinely curious as to why you said that?
My understanding is he was saying the opposite, from the quote I posted?