StonelaughterMemberFeb 22, 2007 at 2:13 pm #122759
OK, I have an Access 2007 Database, design as below. Cascade update fields is on every relationship, cascade delete records is not. The Join Type of all relationships is type 1: include only rows where both join fields are equal. The primary keys for Supplier, Category and Sales are all autonumber and they link to long integer fields in the “Many” side of the relationship. The “Category” field in the “Supplier” table looks up from the “Categories” table – it’s the main product category which is bought from that supplier. As you can see from the diagram, there’s no referential loop.
I have added two supplier records and several category records. When I attempt to add a “Product”, I enter the details. Where a detail comes from a table (category, supplier) I use a drop-down-box to enter it, and select an available value. I display the text name of the detail, but store the “Bound Column” as the primary key. When I try to leave the new record, and commit it to the DB, it says “Unable to add a record now because a related entry is required in table
“, where “nnnn” alternates randomly between supplier and category.
The “Default Value” for the fields is “Null” so that it doesn’t pre-select a value for you and you MUST enter one.
I’ve done this a thousand times and I cannot for the life of me think what I might be doing wrongly. I have tried all three “Join Types” and it makes no difference. Anyone any clue at all? Please? It’s doing my nut in.
You must be logged in to reply to this topic.