Many-to-many - Joint table and form (1 Viewer)

Frank245 · Nov 29, 2017

The records does not update on the link table.

I have two tables (tblBOM and tblBOMCustomer) linked through a joint table (tblBOMCustomerLink). (database attached)

tblBOM
->BOMPartID (PK)
->BOMFileNo
->BOMTitle

tblBOMCustomer
-> BOMCustomerID (PK)
-> BOMCustomerName

tblBOMCustomerLink
->LinkBOMID (PK)
->BOMPartID
->BOMTitle

So I created a form (datasheet view) and inserted a query as a subform(datasheet view) with the "Link Master Field" = BOMCustomerID and "Link Child Field" = BOMCustomerID

The relationship is 1 to Many as shown on the picture (relationship.png) attached.

So my question is, why it doesn't update the joint table by adding new records in the subform (datasheet view)?

MarkK · Nov 29, 2017

The main form should be based on the parent table only, Customer or BOM, since in the many-to-many relationship, both of those are parents.

The subform should be based on the join table only, and the trick is that on the foreign key you put a combobox. That combo box serves as a lookup to the other parent table, and allows selection of the foreign key in the join table.

See the attached Db that demonstrates a solution...

hth
Mark

Pat Hartman · Nov 29, 2017

The junction table does not contain the correct fields;
tblBOMCustomerLink
->LinkBOMID (PK)
->BOMPartID
->BOMTitle

Should be

tblBOMCustomerLink
->LinkBOMID (PK)
->BOMPartID (FK to BOMPartID)
->BOMCustomerID (FK to tblBOMCustomer )

Frank245 · Nov 30, 2017

MarkK said:
The main form should be based on the parent table only, Customer or BOM, since in the many-to-many relationship, both of those are parents.

The subform should be based on the join table only, and the trick is that on the foreign key you put a combobox. That combo box serves as a lookup to the other parent table, and allows selection of the foreign key in the join table.

See the attached Db that demonstrates a solution...

hth
Mark

Thanks.
This solution looks simple.
However, is it possible for the subform to be linked to more than the "joint table" alone? Because in this situation, the main form is just a selector of items already on the tblBOM. I intended to add a new component under the selected customer on the datasheet view form, that means to add information on the tblBOM.

arnelgp · Nov 30, 2017

add the customer id in bomlink.
make th customer the main form.
the bomlink as subfoem.
make the partid of subform combo.
on this combos not in list event, show an
add form for the new part for the main bom.

Pat Hartman · Nov 30, 2017

However, is it possible for the subform to be linked to more than the "joint table" alone?

Yes. In that case, the subform would be bound to a query. I've attached another sample database. It shows the junction from each side and how the subform changes slightly.

Jimis · Feb 15, 2022

Hi all

I have an issue with a many-to-many relationship and a join table. The subform contains the join table. My objective is to be able to update many fields in different tables from a single form. The proper way I read is to use subforms.

But when I try to add a new record I get errors. I can't make a new record in subform (screen2).
My guess is that because of the join table I can't add a "Device Customer" without adding simultaneously a "CustomerERP" because they are linked ? Maybe I could select both from a listbox which I don't want to do.

Same thing I can't add a new record in the Assets parent form either (screen3), I get an error related to Customer join table. Why? "CustomerID" field in AssetsT is not required.

One solution would be to delete the join table and add the two linked tables to AssetsT directly but I would like to know if there is another way around cause that might not be possible with other cases.

I have attached my db file

Thanks in advance

mike60smart · Feb 15, 2022

Hi Jim

See the attached example

Jimis · Feb 15, 2022

Hi Mike

Thanks for the reply.

I can see you added "AssetsID" in Customer_joinT, I get it.

I added "AssetsID" to DevicesT also and changed the relationship of one-to-many to opposite direction cause 1 Asset can have many Devices. There is an extra table "AssetsT_1" that appeared automatically I don't know why.

I still have the following problems.

1) In form Assets222 I can add "Device Customers" and "CustomerERP" in the subform without showing an error but I can't add an asset (eg input plateNo)

2) I can't delete asset (says it's related to table Customer_joinT probably because of the AssetsID field which can't be deleted)

Jim

mike60smart · Feb 15, 2022

Hi

See this version

Jimis · Feb 15, 2022

Mike

The Plate No combo is not really needed as I could change the textbox right below it to show the possible values and be able to add new ones. Also the Plate No in Devices subform I don't need I will delete it anyway this field. But Plate No is not my problem.

The problem is that I can't make a new asset. I just mentioned Plate No because that's the data you input to make a new asset. Example below I make new record and add a Plate No "EKA-4444" and I get an error when I try to fill Device Customer and CustomerERP fields

Jim

mike60smart · Feb 15, 2022

Jimis said:
Mike

The Plate No combo is not really needed as I could change the textbox right below it to show the possible values and be able to add new ones. Also the Plate No in Devices subform I don't need I will delete it anyway this field. But Plate No is not my problem.

The problem is that I can't make a new asset. I just mentioned Plate No because that's the data you input to make a new asset. Example below I make new record and add a Plate No "EKA-4444" and I get an error

Jim

Hi Jim

You are not understanding how Form work

When the database Opens it shows a current record and you would not be changing the value displayed.

When you need to add a New Asset you would use a Command Button to add an Asset or

You can set the Form so that when it opens it displays Blank - No record.

You enter the data required and then close the Form - this saves the data to the underlying Bound table.

The reason you were getting the error was that you had fields in the Assets table that were not required. ie CustomerID, Notes etc...

I deleted these and it now works as it should.

Pat Hartman · Feb 15, 2022

Here's a sample I made to show how m-m relationships work. It shows two different methods of visulalizing related data. From tblA to tblB, I use a main form with a sub form, which is the typical example. But from tblB to tblA, I use a pop up form which is an alternate method. Both are correct. Use whichever method makes sense in your situation.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

GPGeorge · Feb 15, 2022

My objective is to be able to update many fields in different tables from a single form. The proper way I read is to use subforms.

Unfortunately, that suggests you are willing to ignore the best practice in favor of the hard road.

You might want to pay attention to Pat's suggestions and sample download. That's not only "the proper way", it's the simplest.

Here's a blog post that talks about how Frank Sinatra saw such choices.

Pat Hartman · Feb 15, 2022

I should point out that you should download the newest version rather than the old version as long as you are using ACE rather than Jet.

Jimis · Feb 16, 2022

ok I found the problem. I couldn't make a new record because the "AssetNotesID" field in AssetsT was indexed No Duplicates (leftover from previous try to add many-to-many relationship). Ofc it's not needed but at least I know why this wasn't working.

I'm still trying to get the hang of the relationships, I think I'm almost there but I have a problem. I can't seem to make a one-to-many relationship with DevicesT from AssetsT. I made a dummy Table1 same as the DevicesT (a copy still gives an error) and it works fine.
This is prbly a leftover again but I need to find it so I can solve future problems.

I have uploaded my db.

GPGeorge
I'm not sure what you mean by best practice. Do you mean having to call multiple forms to update fields like in Pat's example ? That can be bad for the user + loss of time in clicks + possible mistakes or forgets. Instead you can have all he has to update in one form with subforms.

Thanks for the help

Jimis · Feb 16, 2022

I found the problem. It was a data problem in DevicesT. An AssetID was 0 which was non-existent.

Making changes to an existing db is a pain

Pat Hartman · Feb 16, 2022

Here is a database that lets you fix up some of the more stupid default settings chosen by MS. These buttons update your database schema. MAKE A BACKUP BEFORE RUNNING ANY OF THEM!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

mike60smart · Feb 16, 2022

Jimis said:
ok I found the problem. I couldn't make a new record because the "AssetNotesID" field in AssetsT was indexed No Duplicates (leftover from previous try to add many-to-many relationship). Ofc it's not needed but at least I know why this wasn't working.

I'm still trying to get the hang of the relationships, I think I'm almost there but I have a problem. I can't seem to make a one-to-many relationship with DevicesT from AssetsT. I made a dummy Table1 same as the DevicesT (a copy still gives an error) and it works fine.
This is prbly a leftover again but I need to find it so I can solve future problems.

I have uploaded my db.

GPGeorge
I'm not sure what you mean by best practice. Do you mean having to call multiple forms to update fields like in Pat's example ? That can be bad for the user + loss of time in clicks + possible mistakes or forgets. Instead you can have all he has to update in one form with subforms.

Thanks for the help

Hi Jim

Looked at the uploaded Db and you appear to be going backwards with your design changes.

What has happened to the table with the list of Devices?

Jimis · Feb 17, 2022

mike60smart said:
Hi Jim

Looked at the uploaded Db and you appear to be going backwards with your design changes.

What has happened to the table with the list of Devices?

I was just experimenting, I was deleting fields trying to find the problem until I found it. Those data are just for testing

Many-to-many - Joint table and form (1 Viewer)

New member

Attachments

bit cruncher

Attachments

Super Moderator

New member

..forever waiting... waiting for jellybean!

Super Moderator

Attachments

New member

Attachments

Registered User.

Attachments

New member

Attachments

Registered User.

Attachments

New member

Attachments

Registered User.

Attachments

Super Moderator

Grover Park George

Super Moderator

New member

Attachments

New member

Super Moderator

Attachments

Registered User.

New member

Similar threads

Users who are viewing this thread