retrieving data from other tables in combo and entering it into a different table

[JFo]

I have attached a simplified version of my database for an example



frmInspections is where my problem lies at this time, if you add a new inspection (or edit an existing one) the data being entered in tblInspections is recorded correctly

However if you select a client or an inspector then it is also adding this data again to the respective tables ie tblClients and tblInspectors

what am I coding wrong so that it does not get re-entered as a duplicate in these tables and just the foreign key is entered into the correct table?

Thanks

Attachment 5009

[June7]

If I understand correctly, purpose of this form/subform is to create a new inspection and select equipment for that inspection.

The RecordSources for the forms are incorrect and controls are bound to wrong fields.

The RecordSource for the main form just needs to be to tblInspections, no table joins required.

Then the ClientName and InspectorName comboboxes should be bound to the ID fields of tblInspections.

The Equipment combobox on the subform should be a multi-column combobox. Primary key for tblEquipment is EquipmentID. This is the value that should be saved to tblEquipmentInspectionJunction. That, or change the primary key to EquipmentNo.
Name - cboEquipment
RowSource - SELECT tblEquipment.EquipmentID, tblEquipment.EquipmentNo, tblEquipment.Description FROM tblEquipment;
ColumnCount - 3
ColumnWidths - 0";.5";1"
BoundColumn - 1
ControlSource - EquipmentID

Two options for getting the Description on the subform:
1.
The subform RecordSource just tblEquipmentInspectionJunction.
Then the Description combobox should just be an unbound textbox with ControlSource: =cboEquipment.Column(2)
This might be exhibit a data refresh delay on the form.
2.
The subform RecordSource would be:
SELECT tblEquipmentInspectionJunction.*, tblEquipment.Description
FROM tblEquipmentInspectionJunction LEFT JOIN tblEquipment ON tblEquipmentInspectionJunction.EquipmentID = tblEquipment.EquipmentID;
Then the Description would be bound to the Description field. I did not think field would be editable but it is allowing edit. So set Locked Yes and TabStop No.

[JFo]

The RecordSources for the forms are incorrect and controls are bound to wrong fields.

The RecordSource for the main form just needs to be to tblInspections, no table joins required.

Then the ClientName and InspectorName comboboxes should be bound to the ID fields of tblInspections.

Thanks June7 I knew it was simple but I couldn't figure it out, this is exactly what I was after