Update New Record In junction table with VBA (without subform) (1 Viewer)

Deepak.Doddagoudar · Mar 31, 2018

I have many to many relationship between tblMachine, tblMachineSystem,tblMachineSubSystem and tblComponents. There three junction table between them. I have a form with four listboxes for each table. When i click a button under the first listbox (listMachine) it gives me the items in the second listbox (listMachineSystem) and it continues till listComponents.
I also have a text box where in i type new machine name and when i click a button the tblMachine is updated with new machine.
My requirement is the New machine created must also populate the first junction table (between tblmachine & tblMachineSystem) with the new machine ID and existing Machine System ID.
MachineID MachineSystemID
1 2
1 2
2 1
2 2
From the above description the first two rows is created manually. Now I want to assign the ID 2 for New Machine and link it to the old ID of Machine System.

arnelgp · Mar 31, 2018

Why are the 2 rows have same machine and sysmachineid?

Deepak.Doddagoudar · Mar 31, 2018

I am sorry I made a mistake, here its actually
1 1
1 2
2 1
2 2

arnelgp · Mar 31, 2018

Still dont follow as to why 2 same machine id.

Deepak.Doddagoudar · Mar 31, 2018

Thats because in the beginning I am assigning one machine to two machine systems (1,1) and (1,2). And then I am creating a new machine, giving it an autonumbered ID 2 in the tblMachine and then assigning the previously available MachineSystem ID's, hence (2,1) and (2,2), in the Junction table.

jdraw · Mar 31, 2018

Deepak,

I am a student doing my masters and right now i am carrying out a project in MS Access

Can you post the requirements for this project?

Can you step back and tell us in simple, plain English what you are trying to accomplish/automate?
No tables , junction or listbox ---just plain English.
Tell us about machines, machine systems and subsystems and components.
Perhaps an example that puts all these "things" into some business context would clarify the requirement.

Good luck with your project and studies.

Deepak.Doddagoudar · Mar 31, 2018

You see from the attachment. There is this tree (blue) that was already existing in the excel. I have put this in the database. Initially you will feel that it is one-to-many relationship but the requirement is, to a newly created machine (black circle) you add some or add all the machine, machine system and components.
Now it is many-to-many.
Its like a lego where in you build new combination but by changing the base only. The base in my case is the Machine

arnelgp · Mar 31, 2018

you should have an Insert Query just like this.
remember to replace "junctionTable" with correct name of your
junction table:

INSERT INTO junctionTable ( MachineID, MachineSystemID )
SELECT (T1.MachineID)+1 AS Expr1, T1.MachineSystemID
FROM junctionTable AS T1
WHERE (((T1.MachineID) In (SELECT Max(t2.MachineID) FROM junctionTable AS t2)));

Deepak.Doddagoudar · Mar 31, 2018

In the form i have created a text box with a button to populate the new machine into tblMachine and Junction Table. The new machine created is populating the tblMachine but not Junction table

Deepak.Doddagoudar · Apr 1, 2018

When you mentioned "SELECT (T1.MachineID)+1 AS Expr1", what is 1 AS Expr1? I beg your pardon if this is a silly question.

arnelgp · Apr 1, 2018

Its adding 1 to the value of machine id. I just followed your first sample but you can remove it to get the correct value you need to insert in the junction table.

Pat Hartman · Apr 1, 2018

I posted a sample m-m database for you in a different thread. In it you see that the subform is bound to the junction table and includes a combo that allows you to pick the FK from the right-side table.

Deepak.Doddagoudar · Apr 2, 2018

Could you please provide the link to this thread?

arnelgp · Apr 2, 2018

Remove the +1 on the insert sql that i gave you.
Replace the strSql on your code with the insert query.
Execute the insert query on your code:

strSql= "the isert sql i gave (+1 removed)"
currentdb.execute strsql

Deepak.Doddagoudar · Apr 2, 2018

I did as you told but slightly changed the code. I am trying to use the Dmax to retrieve the last added record. Is this approach right? I seem to be a bit lost here.
What ever text i am adding in the text box to add new machine i am getting an error saying "the changes would create duplicate values" but is still updating the tblMachine and highlighting "tblMachine.update" in the code. I have attached the code for you.

arnelgp · Apr 2, 2018

Its better u upload a sample db so we can understand it better.

Deepak.Doddagoudar · Apr 2, 2018

I have attached the database. The requirement is from "Create New Machine" Form if you create a New Machine (top right), say "Machine B" and click on "Add Machine" button, this new record must populate the tblMachine (which is happening right now) but this same "Machine B" ID must also populate the JunctionTableOne and become the father of the existing children (Machine System A & Machine System B).

arnelgp · Apr 2, 2018

Here take a look

Deepak.Doddagoudar · Apr 2, 2018

Wow! This is exactly what i needed. Thank you very much!

Deepak.Doddagoudar · Apr 2, 2018

Now the next part is trickier. What if I wanted to add new Machine System, say " Machine System C" under the newly added "Machine B".
I have attached your updated database with an extra text box " Add new machine system". This text box is as per my one-to-many relationship (image attached) where in I entered the new machine system and entered the parent Machine ID.
How do i do it for the current many-to-many?

Update New Record In junction table with VBA (without subform) (1 Viewer)

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Super Moderator

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Registered User.

Attachments

Registered User.

..forever waiting... waiting for jellybean!

Super Moderator

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Registered User.

Attachments

Similar threads

Users who are viewing this thread