Problem with many-to-many relationships and forms

[Guest]

OK, here's the basic structure of my database:

Station Table
Each station can have multiple parts (Station prim key unique)

Part Table
Part # prim key unique.

Location Table
ach location can have multiple parts (Location prim key unique)

I then have 2 joining tables and did one to many relationships to setup a many to many relationship as described above as per the help in access. This way for example, Station A1 could have many different parts as well as a certain number of those parts.

The thing is, the data works fine in the tables and subsheets in the tables.

However, when I setup the forms and subform (with the wizard), the data appears correctly (ie when I change the station, the various parts belonging to it show up.) However, when I use the form and change the station, I can no longer add or even click on the parts subform and it gives me the error

"Cannot perform cascading operation. It would result in a duplicate key in table <name>. (Error 3399)"

The thing is, it brings up the right data, I just can't go in and add another part for that station, etc.

 

[John Vinson]

However, when I setup the forms and subform (with the wizard), the data appears correctly (ie when I change the station, the various parts belonging to it show up.) However, when I use the form and change the station, I can no longer add or even click on the parts subform and it gives me the error

"Cannot perform cascading operation. It would result in a duplicate key in table <name>. (Error 3399)"

I think what you're doing (or trying to do, unintentionally) is to
*REPLACE* the value of the field in the table, whereas I believe what
you actually want to do is *NAVIGATE* to a different record... is that
correct? If so, you need an unbound control.

Perhaps you could post the Recordsource properties of the main and
subforms, and the Master and Child Link Fields.

 

[John Vinson]

Thanks for your help!

You are correct. When I removed the "station" combobox binding to the station, but left only the rowsource, it worked fine.

huh? "left only the rowsource"??? I don't understand what you did.

Now I can't use the scroll through records using the default buttons , but oh well.. with 100's of stations that's not particularly effective anyway.

And I don't understand this EITHER. Perhaps you could explain a bit
more!?

QUick Q: If I'm using the setup I've mentioned, the only way to "add" a new part would be to do so in the table or a form with only the "parts" table, I can't make it so I can add a new part within the linking table or form?

You can use the "Not In List" event of a combo box to pop up the Parts
form when you try to enter a nonexistant part.