Can't update data using form keep getting message saying will create duplicate values

[alexpmi]

Hello, I don't know much about Access, so I would first of all, like to apologize if my question is silly and obvious, but I have been trying for some time and can't figure it out.

I am creating a database to keep track of a set of art pieces. Each piece has a series of attributes, one of which is style, a piece can have more than one style.

I have created one table with a series of fields for each piece (name, author, etc.) which has a field called PieceID as the primary key and another table with the different styles which only has two fields, StyleID (Primary key) and StyleName.



I have created a third junction table which has two primary keys, PieceID and StyleID to link the two tables. The two tables are linked one-to-many to this table via their respective primary keys.

I wanted to have a form to introduce and edit all the info for the pieces and when it comes to choosing the style be able to choose from the list of existing styles. I thought of using a form with the main table fields and then a subform with the different styles for the piece. The problem is I wanted this subform to only have one combobox which would use the list of existing Styles as its source, so I can choose StyleName not StyleID . I can't figure out how to do this and get it to work.

Any help would be greatly appreciated.

[June7]

With main form bound to Pieces, subform should be bound to junction table and have a combobox to select style.

Combobox properties:

RowSource: SELECT StyleID, StyleName FROM Styles ORDER BY StyleName;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1.0"
ControlSource: StyleID (field from junction table - advise not to use exact same field name in multiple tables)

This will allow selecting style by name but save ID.

Be sure to set subform Master/Child Links properties.

[Micron]

You can have a pk field in the junction table, but the 2 other fields should be foreign keys, not primary. You would not be able to repeat any given value from either of the 2 related tables in the junction table. Or did I misinterpret the original post?

[davegri]

Have a look at this link to see examples of handling many-to-many tables and data entry.

Many-To-Many, two ways to update Junction table, includes Not In List handling (accessforums.net)

[June7]

Micron, I expect junction table has compound primary key using two number fields which should be fine although could just be compound index.

[Micron]

I considered that. Could also be exactly as described

a third junction table which has two primary keys

which is pretty much guaranteed to cause the issue.

[June7]

Isn't two primary keys impossible? Whereas a compound key is possible and not an issue, just not needed unless junction table will have related child table. I have used compound key only once.

[Welshgasman]

I have created a third junction table which has two primary keys, PieceID and StyleID to link the two tables. The two tables are linked one-to-many to this table via their respective primary keys.

I believe the O/P means they are FKs from their respective table PK?

[xps35]

I have created one table with a series of fields for each piece (name, author, etc.) which has a field called PieceID as the primary key and another table with the different styles which only has two fields, StyleID (Primary key) and StyleName.
I have created a third junction table which has two primary keys, PieceID and StyleID to link the two tables. The two tables are linked one-to-many to this table via their respective primary keys.

Can you please share a copy of your database?

[Micron]

Isn't two primary keys impossible?

Don't think I've ever tried. Did so just now and you're correct, so OP is not - technically speaking, that is.

[CJ_London]

Perhaps op is talking about a multi field pk?

[Welshgasman]

Time to upload the DB methinks?