Adding New data to Composite table via multiple multiselect listboxes

[kd2017]

Yes the Machine has many Machine System. MAchine System has many MAchine sub System and so on.

I think you misunderstood my question. Does a MachineSystem have many Machines?

Duplicating the data is okay for us.

Naughty rule breaker

We will create a New Machine and its ID has to now be populated in table MAchine iD of the MAchine System table.
Attachment 33135
Similarly the MAchine System ID 15 from the table Machine System should be populated in the Machine system ID field of the table MAchine subsystem for the MAchine Subsystem ID 42 and 43.

So you have your three (or four) list boxes and the idea is that you use the list boxes to select existing data from the relevant tables and then click a button to duplicate them in each table and then associate the duplicates back to ONE new machine? Lets see the code you're using to create the new records.

[Deepak.Doddagoudar]

Yes you are exactly right. in saying this :"So you have your three (or four) list boxes and the idea is that you use the list boxes to select existing data from the relevant tables and then click a button to duplicate them in each table and then associate the duplicates back to ONE new machine? Lets see the code you're using to create the new records". But for the new machine you cannot use the old ID's of the associated data since they are already linked to the existing machine. We need new ID's for new machine created.
Here is the code:
Private Sub CREATEMACHINE_Click()

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Dim rs2 As DAO.Recordset
Dim ctl2 As Control
Dim rs1 As DAO.Recordset
Dim ctl1 As Control


On Error GoTo ErrorHandler


Set db = CurrentDb()
Set rs = db.OpenRecordset("tblMachineSystem", dbOpenDynaset, dbAppendOnly)
Set rs1 = db.OpenRecordset("tblMachineSubSystem", dbOpenDynaset, dbAppendOnly)
Set rs2 = db.OpenRecordset("tblComponents", dbOpenDynaset, dbAppendOnly)

'add selected value(s) to table
Set ctl = Me.listMachineSystem
Set ctl1 = Me.listMachineSubSystem
Set ctl2 = Me.listComponents

For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!MachineSystem = ctl.ItemData(varItem)
rs.Update
Next varItem

For Each varItem In ctl1.ItemsSelected
rs1.AddNew
rs1!MachineSubsystem = ctl1.ItemData(varItem)
rs1.Update
Next varItem

For Each varItem In ctl2.ItemsSelected
rs2.AddNew
rs2!Components = ctl2.ItemData(varItem)
rs2.Update
Next varItem




ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub


ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select

End Sub

[kd2017]

You'll need to change to nested loops so that is you create each parent record you loop to creat it's child records and save them with the appropriate parent I'd all at the same time.

[isladogs]

You may find it helpful to look at the nested recordset example I posted in this thread
https://www.accessforums.net/showthr...083#post391083

Not the same situation as yours, but the code may help you anyway

[Deepak.Doddagoudar]

I understand what you are telling me but to put it in the form a code it is a bit difficult. Is it possible to send a sample code by relating to our case.

[isladogs]

If that is directed at me then sorry. Not today. Busy on other projects

[kd2017]

I'm away from my computer at the moment so it would be difficult to code up an example. If no one has chimed in before this evening I'll be glad to help.

[Deepak.Doddagoudar]

No problem. I will wait for your reply. Thank you.

[kd2017]

33122[/ATTACH] Attachment 33123 Attachment 33124

Another problem with this: how do you know which [Machine System ID] belongs to each new Machine Subsystem? And which MachineSubSystem belongs to each new Component?

Anyway the nested loop I was referring to in post 18 would work something like this:

For i1 = 0 to something '<Looping through your list of records to add>
  rs.AddNew
  rs!Field1 = ...
  rs!ForegnKeyField = ... '<your new machine id would go in the first foreign key field>
  rs.update
  rs.Bookmark = rs.LastModified 'When inserting a new record into a recordset, the new record does not automatically become the current record. To access the new record, move to the bookmark LastModified

  For i2 = 0 to something
    rs1.AddNew
    rs1!Field1 = ...
    rs1!PARENTKEY = rs![ID FIELD] '<the ID just generated when you ran rs.update above>    
    rs1.Update
    rs1.Bookmark = rs1.LastModified

    For i3 = 0 to something
      rs2.AddNew
      rs2.Field1 = ...
      rs2!PARENTKEY = rs1![ID FIELD]
      rs2.Update
      'rs2.Bookmark = rs2.LastModified

    Next i3
  Next i2
Next i1

Make sense?

[Deepak.Doddagoudar]

Exactly that is the trickiest part. Manually we used to look into the previous table and enter the ID into the next table. Anyways I will look into the code that you have sent and get back to you. Thank you again!

[Deepak.Doddagoudar]

This is how it should be in the end.
In the beginning there was only one New Machine in the tblMachine and you linked its ID to all newly created Machine System in tblMachineSystem, this level is simple. But trouble starts in the next level. There are multiple newly created Machine Systems and you will have to link it properly to the newly created Machine SubSystems in the tblMachineSubSystem. Similarly to the next level to components.
I thought of an idea, is it possible to match the text ? I mean only the ID has changed but the text remains the same isn't it?

[kd2017]

What's the logic behind that association? MachineSubSystem #1 & 2 belong to the same MachineSystem #1...

Can you give us an explanation of what your doing in real world terms? Forget the database for a moment and just tell us what it is your doing? What's the step-by-step process as you'd explain it to a new coworker?

[Deepak.Doddagoudar]

• Go to tblMachine see the Machine ID of the newly createdmachine and then go to tblMachineSystem and enter the noted numeric ID value (for the newly added Machine system) in the Machine ID field
  
• Now from the tblMachineSystem see the Machine System ID of the newly createdmachine system and then go to tblMachineSubSystem and enter the noted numeric ID value(for the newly added Machine SubSystems) in the Machine System ID field.
  
• Now from the tblMachineSubSystem see the Machine Sub System ID of the newly createdmachine sub system and then go to tblComponents and enter the noted numeric ID value(for the newly added Components) in the Machine Sub System ID field.
  
• Save and close

[kd2017]

No no no. Forget about the database for a moment and explain in plain English what your doing.

It's easy enough to do as you've explained in the post above when your only adding one record at a time in the system and subsystem tables. But as I understand it your adding multiple.

It's absolutely possible to not have to duplicate the text, that's what I've been telling you from the beginning. Watch some videos on many-to-many relationships.

[Deepak.Doddagoudar]

Okay i understood the many to many relationship more clearly now. Instead of duplicating the text in the tables we are duplicating the IDs in the junction box table. Now even if I create three different Junction tables will have to some how automate populating new records ID.