Query built from many to many relationship not updating - NEW TO ACCESS

[dqamar2]

I have three tables:



PartList table
DrawingsList table
and Connector_Drawing table

they are related like this




(every connectorID is linked to a DrawingID) and the data is displayed correctly in the Connector_Drawing table:

I create a query that shows the BasePartNumber from (PartList table) and the DrawingNumber from (DrawingsList table).





This data shows correctly as well. My relationship worked correctly.
Now I want to add new relationships, eg BasePartNumber 1 is also related to DrawingNumber 2 in addition to drawing 1.

How do i do this? I keep getting the following error: "Cannot add record; join key of table 'DrawingList' not in recordset."






Ultimately I will be adding new parts and new drawings and connecting both of them through a form using this query (unless there is a better way)

Thank you for any help! I have been going crazy over this the last few days.

[June7]

Advise not to work directly with tables and queries. Use forms for data entry.

Each part can have multiple drawings and each drawing can associate with multiple parts?

If you want to add new relationship in Connector_Drawing then need that table's fields in the query. The only table needed in the query is Connector_Drawing. The PartList and DrawingList tables would be lookup sources for dropdown lists (comboboxes).

Options for form design:

1. one form bound to Connector_Drawing with comboboxes to select BasePartNumber and DrawingNumber

2. main form bound to PartList and subform bound to Connector_Drawing with combobox to select DrawingID

3. main form bound to DrawingList and subform bound to Connector_Drawing with combobox to select ConnectorID

[aytee111]

Remove the ConnectorDrawing table, it has no additional use. Put the ConnectorID on to theDrawingList table.

At what point/under what circumstances is the error occuring?

[dqamar2]

June7

Yes each part can have multiple drawings and multiple drawings can go with different parts.

I try to create a form and run into the same issues.
I tried to create forms where I display the part number and drawing numbers - the source of the form was the query.
When I add an entry it does not store that to the Connector_Drawing table. It actually does not even let me add an entry.This is the error I get: "The changes you requested to the table were not succesful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field..."

[dqamar2]

The problem is this cnnectorID will be used in several other tables/forms as well. I want to keep the data separate and call them using different queries/forms

[June7]

Can't advise without knowing exactly what you tried. Did you build forms as described in my earlier post? The query you posted does not fit my described models and would not work.

If you want to provide db for analysis, follow instructions at bottom of my post.

[dqamar2]

So did the following (option 2 from June7):
Made the query from the two fields in Connector_Drawing (don't really know why, it is the same exact information as Connector_Drawing)
Created a Form bound to PartsLIst
Created a subform bound to Connector_Drawing - where I show 4 text boxes. ConnectorID and DrawingID (from the Connector_Drawing table). I also show BasePartNumber and DrawingNumber (from the PartsList table and the DrawingList table)

All data is showing correctly on my form.
All relationships I defined in the Connector_Drawing table are showing correctly.
BUT I am still not able to add a NEW relationship. I tried using the "Add new record" button but still no luck.
It gives me the following message now: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again"


Ignore all the hidden forms/tables.
Thanks in advance!

[June7]

If you are trying to build option 2 structure, only Connector_Drawing table is needed in the subform RecordSource and then need combobox bound to DrawingID field from Connector_Drawing in the subform to select DrawingID.

Advise you give controls names different from the fields, like cbxDrawing.
Textbox for DrawingNumber is not needed because this value will display in the combobox.

If you really want to display the drawing type, then set combobox properties:
RecordSource: SELECT DrawingID, DrawingNumber, DrawingType FROM DrawingTypeList RIGHT JOIN DrawingsList ON DrawingTypeList.DrawingTypeID = DrawingsList.DrawingTypeID;
ColumnCount: 3
ColumnWidths: 0";1";0"
BoundColumn: 1
TabStop: No

Then a textbox can reference column of combobox: =[cbxDrawing].[Column](2)

No need to have any textboxes displaying ConnectorID, this value has no meaning to users.

Don't think really need the move next button on the subform.