Form based on query (many to many) adding values to table?

[kjschmitz]

I have three tables in a many to many relationship.

tbl_Projects which contains the Project_ID (PK - autonumber) field and Project_Name (text) field.

tbl_Contractors which contains the Contractor_ID (PK - autonumber) field and Contractor_Firm (text) field

tbl_Eligible_Bidders which contains both Project_ID (FK - number) and Contractor_ID (FK - number) fields as conposite index. This table acts as an associate table for my many to many relationship.

Each Project can have any number of eligible bidders (contractors) and each contractor can bid on any number of projects.

I then created a query (qry_Eligible_Bidders) based on these tables as the following:

SELECT tbl_Eligible_Bidders.Project_ID, tbl_Eligible_Bidders.Contractor_ID, tbl_Projects.Project_Name, tbl_Contractors.Contractor_Firm
FROM tbl_Projects INNER JOIN (tbl_Contractors INNER JOIN tbl_Eligible_Bidders ON tbl_Contractors.Contractor_ID = tbl_Eligible_Bidders.Contractor_ID) ON tbl_Projects.Project_ID = tbl_Eligible_Bidders.Project_ID;

Now I am trying to create a form that will allow me to add contractors to projects. I started by creating a form based on the tbl_Projects table. Next I added a subform using qry_Eligible_Bidders linked by the Project_ID fields found in both.

Next I assign a Contractor to a Project - using a combobox with the following as it's row source:

SELECT tbl_Contractors.Contractor_ID, tbl_Contractors.Contractor_Firm
FROM tbl_Contractors
ORDER BY tbl_Contractors.Contractor_Firm;

I have the bound column set to 1, Column count set to 2, and Column Widths at 0",1".

When I do this, the associated table updates correctly, but I also seem to be adding a record to my tbl_Contractors. Any help would be greatly appreciated.

 

[lameid]

Next I added a subform using qry_Eligible_Bidders linked by the Project_ID fields found in both.

Without looking really closely at the why you are getting duplicates, the subform should be based on the table tbl_Eligible_Bidders not the query. Just use comboboxes to display the data you want in the subform.

 

[dhookom]

I would use a subform based only on tbl_Eligible_Bidders. You shouldn't need tbl_Projects or tbl_Contractors in the subform's record source.

Duane
Hook'D on Access
MS Access MVP

 

[kjschmitz]

This seems to work, but now I am having challenges writing the combobox row source statement to pull in all available contractors, even if they haven't been assigned to the tbl_eligible_bidders table yet.

I'll mess around a little more before coming back with another question however.

Thanks to both of you for the help so far!

 

[dhookom]

The Row Source of the combo box should be based on the contractors table like:

SELECT Contractor_ID, Contractor_Firm
FROM tbl_Contractors
ORDER BY Contractor_Firm;

You could change this to eliminate contractors already selected.

Duane
Hook'D on Access
MS Access MVP

 

[kjschmitz]

Thanks again lameid, and hookom. Both of you were correct and it is working as expected now. I don't know why I always insist on making things more difficult than they need to be.

Much appreciated, both of you.