The Dreaded "You Can't Assign a Value to This Object"

[lacey]

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. I can't get the stinkin' Categories subform to work right on my main form. In my readings online, I found a ton of people posting about this issue, but I can't make sense out of any of the solutions they posted. My link main form/child form fields are CategoriesID...

When I try to click into a checkbox as the user in the subform, I get this error: "You can't assign a value to this object. The object may be a control on a read only form. The object may be on a form that is open in Design View. The value may be too large for this field." And, as others have experienced, none of these apply (to my knowledge).

Here is my database. Help is so appreciated! View attachment Contacts Example.accdb

 

[Mihail]

Change the row source for the subform to tblCategories

 

[lacey]

Thanks Mihail. That cleared the error message. Now I have another issue I'll make a new post for. It never ends... :banghead:

 

[JHB]

My link main form/child form fields are CategoriesID...

Yes here is the problem, because you haven't any value in the CategoriesID on the main form.
To link a main form and child form required a value on the main side. Creating a record in the child form doesn't set a value in the main form's link field.
You need to rethink your database setup.

 

[lacey]

Thanks for your reply JHB. So, should my subform be linked to the pk in the main form isntead, which is an auto number? That would ensure there was always a value to connect them.

I'm a bit lost. Any advice would be helpful!

 

[jdraw]

Please tell us in plain English WHAT your business is about. Keep it real simple so readers can understand the issue/opportunity in context. Your database should support your business, so understanding you business is step 1.
Companies, Categories and Contracts ---these things must be related by some business rules.

 

[lacey]

We (my director and I) want to use this database to store her hundreds of business contacts. Most contacts will have a company (but not always), and every contact will have a category of some sort (whether it's one check box or several).

I created tblContact to store the contact's information, tblCompany to store the company information, and tblCategory to store the category information. Since each contact will have a category (but may not have a company), I am thinking I need to change things so that tblCategory is related to tblContact, not tblCompany as I have it set up now.

Let me know if I can clear anything else up.

 

[JHB]

I need to change things so that tblCategory is related to tblContact, not tblCompany as I have it set up now.

Yes - exactly!

 

[lacey]

Okay, so I created a new table -- tblContactCategory. PK is ContactCategoryID with fields ContactID and CategoriesID. I have them linked to tblContact and tblCategories by each respective table's PK to create two one-to-many relationships.

Now, back to the form... I have tried to add a subform to the main form, creating the subform from tblCategories through the subform wizard. Link Master fields is ContactID and Link Child fields is CategoriesID (does that sound right?). When I try to check one of the boxes it works -- but when I close the whole form and open it again there are no boxes checked. So, something is still off. Any ideas?

Thanks so far!

 

[jdraw]

Can you post a jpg of your latest Relationships window?

Contacts is your main subject; a Contact may be related to 0 or more Companies
Can a Company be assigned to Many Categories (many to many)?
Based on your database from post #1, I think tblCategories should be

CategoryId PK
CategoryTest

 

[lacey]

Here is the relationships: View attachment Report1.pdf

Contacts is your main subject; a Contact may be related to 0 or more Companies

Correct.

Can a Company be assigned to Many Categories (many to many)?

Each Company can have many of the Categories boxes checked (they are yes/no fields), but not more than one record. Does that make sense?

 

[JHB]

Can you have Categories when you don't have a company?
If not, then you should lock for input of Categories until a company is filled in.

See the attached database.

 

[lacey]

Actually, a contact can have a category without having a company. So, this won't work. Any other ideas?

 

[JHB]

I'm little confused, therefore I need to ask the following:
A contact can have 0 company attached, but if a contact have a company attached then only 1 company? When I look at the relationship between Contact and Company, then you have set it up that a contact can have many companies!!

So Categories are actually "hanging" on a contact and not on a Company like now? Then a contact can only have one set of Categories, isn't it correct?