Subform not connecting to main form correctly

[darkyetlovely39]

Hi,

I have a form based on tblContact with a subform based on table
tblCategories, and another subform based on tblCompany. I have a one-to-many
relationship between tblCategories and tblCompany, with the pk in tblCategory
linking with a fk in tblCompany. All of the fields in
tblCategories/sbfrmCategories are yes/no fields, not sure if that plays into my
problem at all.



When a user checks one or more of the check boxes in the
categories subform, closes the form and re-opens it, none of the check boxes are
checked for that person. I know this has something to do with the relationships
and how the categories are linked to the contact... but I can't get it straight
in my mind as to how that should be set up.

Here is my database. Help and advice for next steps is appreciated! Contacts Example.zip

[June7]

The data structure for categories is not correct.

Can a company or contact be classified as more than one category?

[darkyetlovely39]

A company or contact can have more than one of the category boxes checked, is that what you mean?

[June7]

Yes, and this is a poor design. Multiple yes/no fields can be difficult to work with especially when all the fields are the same type of data. Also, if you want to add new category, that would mean a new field in table and revising queries, forms, reports, code.

This data relationship is many-to-many. The conventional approach is a junction table to associate the entities.

Consider:

tblCompanies

tblContacts

tblCategories
Category

tblCompanyCategories
CompanyID
Category

tblContactCategories
ContactID
Category