UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Many To Many Relationship, How To Update Junction Table Using Subform, Access 2013    
 
   
LBELL1091
post Apr 19 2019, 05:53 PM
Post#1



Posts: 3
Joined: 17-April 19



Hoping someone can help with a many-to-many database question. I am having difficulty getting a junction table to update when adding records to a form.

My database has a Bids table that is linked to a Bid Items table with a junction table. The junction table only contains the primary key from both of the tables.

The junction table is used because the Bid Items can be on a master Bid and on a change order Bid for the same job. Both Master Bids and Change Order Bids are in the Bids table. I want to ensure that if a Bid item is updated on the master Bid that the item change is also on the Change Order Bid so I do not want to duplicate the Bid items. Thus, I thought linking the Bids table to the Bid Items table with a junction table would be the solution. All good until I got to the form design….

My main form has the Bid Table (query) as its source and is a Tab form -- one of the tabs is the Bid items tab that has a subform whose recordsource is a query that contains BOTH the Junction table and the Bid Item table. This subform contains many fields from the Bid Item table in the query. My problem is that I am getting errors when attempting to add a new Bid item on the subform. I have read many posts about adding a combo box to populate the junction table and have tried numerous combinations with no success. All examples I have seen use just the junction table as the source of the form, but I have many fields I must pull from the Bid item table so I need to have both tables in the query that sources the form. I am OK with programming to create the record for the junction table if needed, but always get an error stating a corresponding record is needed in the one side of the relationship before it gets to my code. Currently, I have Referential Integrety on for the relationships, but have also tried with it off.

This is a large existing database that has been used for many many years and the ability to support the change order Bids based off of the Master Bid is something I am adding. The Bid Table and Bid item Tables exist already. My plan was to just add the Junction table.

Any help on how to setup a query and subform on an existing Tabbed form that will update the Bid Items and Junction table would be appreciated! I have tried so many combinations over the past two days with no success. Sorry for the lack of exact error message, but each variation I have tried has produced different errors. Thank you!
Go to the top of the page
 
theDBguy
post Apr 19 2019, 06:36 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

The usual way to update a junction table is to base the subform on it but not include the other parent table to avoid any errors when entering new records. Instead you would use the other parent table through a combobox.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Apr 19 2019, 06:39 PM
Post#3


UA Admin
Posts: 35,123
Joined: 20-June 02
From: Newcastle, WA


One of the downloads on my website illustrates one way to handle many-to-many relationships as theDBBuy describes.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
LBELL1091
post Apr 19 2019, 07:26 PM
Post#4



Posts: 3
Joined: 17-April 19



Thank you for the quick response!

I think I understand the combobox idea, however, the problem I have is that I have many fields I want to display from the one side of the relationship. So, for my Bid Items subform if I base it on my junction table, which only has the primary keys from both of the related tables (Bid and Bid Items), how will I obtain all of the fields for the Bid Items subform?

Was hoping to have all of them in the query so I could easily update or add new records. The subform is a continuous form displaying all items for the Bid. That is why the query I wanted for my subform has the Bid Items table and the Junction table in it. The Bid items are not created separately from a Bid. they are created as part of a Master Bid and then "copied" to a Change Order Bid. So, when they are created in the Master bid, they are a new record in both the Junction table and the Bid item table. When they are copied by the user to a Change order bid, I will just create an entry in the Junction table. It is the initial creation as part of the Master Bid where they will be entering many fields into the Bid item record.

I am fairly proficient in Access, but I am just stumped on this. Is what I am trying to do not feasible?

Many, many thanks for your help.
Go to the top of the page
 
MadPiet
post Apr 19 2019, 09:27 PM
Post#5



Posts: 3,120
Joined: 27-February 09



What table and relationships do you have in your database? Are you getting bids on single items?

ItemForSale(LotID)---(1,M)--Bid(BidID, LotID, BidderID, BidAmount)--(M,1)---Bidder(BidderID)

Each item can have zero or more Bids. Each Bidder can place zero or more Bids. Each Bid must belong to exactly one ItemForSale and one exactly one Bidder.
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2019, 07:52 AM
Post#6


UA Admin
Posts: 35,123
Joined: 20-June 02
From: Newcastle, WA


Please show us the full tables involved. When you feel a need to include multiple fields in a second table, where a single foreign key is enough to identify the related records, that raises the red flag of incomplete normalization.

Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
LBELL1091
post Apr 22 2019, 09:43 AM
Post#7



Posts: 3
Joined: 17-April 19



Thank you to all for your insights and willingness to help! With the assistance of another forum member the problem was identified in the child link on my subform. (The subform query contained both the Junction table and my Bid Items table) The child link needed to be set to the key in my junction table pointing back to the Bid Table.

As this was an enhancement, my Bid Item table still contained the key pointing to the Bid Table so I was using that as the subform child link. With the addition of the Junction table, that field is no longer necessary, but I had failed to remove it from the table and query at this point in the development and it was being used as the child link.


Go to the top of the page
 
theDBguy
post Apr 22 2019, 10:44 AM
Post#8


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th June 2019 - 09:44 AM