Access: adding records to subform with many-to-many relationship

Hello,

I have four tables (categories, CMSFeatures, CMSProducts, ProductFeatures) with the following relationships:



The Features and Products tables are linked by a ProductFeatures junction table.

On the Products form, I have a subform that shows all features associated with a given product. I want to be able to update the subform, in order to add and delete features to and from a given product.

When I attempt to type a new feature into the subform, I get this: "Field cannot be updated."

So I click "OK" and continue to type anyway. When I move the cursor away from the subform I get this: "You cannot add or change a record because a related record is required in table 'categories'."

If you would like to experiment with the actual database, you can download it from here (right-click): http://staff.washington.edu/hlove/CMSeval.mdb.

Can anyone help me?

Thanks in advance,
Harry

Since I have not received any replies, I am removing the image file and the database file from my server.

I am assuming that my question is either:

a) too boring or too easy to answer,
b) not answerable, or
c) interesting, very answerable, but you are trapped under something heavy and can't get to the computer.

Either way, I simplified the database by removing the Categories table. I added a Category field to the Features table. This is not as flexible as I would like it to be, but neither is Microsoft Access. In the future, I will probably use MySQL or PostgreSQL combined with a web interface.

Much thanks to the many people who downloaded the files and made an attempt. I appreciate the effort.

I can't be certain without seeing the diagram (I just joined the forum), but I'm guessing that your categories table was a parent of the products and/or productFeatures table (categoryID existed as a foriegn key in one or both of these tables).

The rules of relationships say you can't create an orphan (a product with a categoryID that does not exist in the category table). Access will enforce this constraint once you create the relationship to preserve data integrity. You may have inadvertantly been trying to do that, which is why when you removed the categories table the problem dissapeared.

Thanks for the reply. Here are the tables:

Categories
catID (pk)
blah
blah

Features
featureID (pk)
catID (fk)
blah
blah

Products
productID (pk)
blah
blah

ProductFeatures
productID (cfk)
featureID (cfk)

Okay. You are adding data to the Features Table, right? Keep in mind that the way you have structured this DB, there are two tasks involved- add a new feature, and associate that feature with a product.

If the data you are updating is a joined view of ProductFeatures with Features this could create a problem (rules for updating Joins can be tricky). So you might want a seperate 'enter new/edit existing feature' form. Adding a feature to a product should be a matter of selecting from a list.

Your first error "field cannot be updated" is talking about a column. I see two possiblities: you are updating a join, or you are trying to enter a value for the featureID and it is an identity (autonumber).

second one is more clear. As I said before, you must have entered a catID that did not yet exist in the Categories Table. Because of the PK-FK relationship, the database will (rightly) not allow you to do this. You must create the category before trying to create a member of it!

Here is the process I'm following:

1) Create all tables and establish keys
2) Establish relationships
3) Create forms for:

• Categories
• Features with Category drop-down box (each feature has only one category)
• Products with Features subform (each product may have one or more features)

4) Create new category data
5) Create new feature data and assign a category to each feature
6) Create new product data and assign features to each product

Step 6 is where the breakdown occurs. I would like to be able to add/delete features to/from a product. The error messages stated in my first post stem from this action.

ah. Divide step 6 into two parts.

6a) create and instantiate product
6b) add features to product

repeat as necessary.

I am guessing here that the problem is you are trying to add product features at the same time as you create the product. Because the product doesn't exist yet, the ProductFeatures table is barfing.

6a and 6b need to be done in that order programatically, but you can hide that from the user.

I can't see what else it could be at the moment.