Duplicating a record and all of its related information in multiple linked bridging tables

[GuysImConfused]

Hello.

A bit of explanation/background before I ask my question:

I am building a database to record configurations of softwares.
I am recording 4 types of software:

1. Application Software
2. Virtual Machines
3. Developed Code
4. Firmware (Equipment Configuration)

Each of these software types has its own table where I record many instances of each.
EG., one software configuration can be made up of 3 application softwares, and 1 virtual machine, while another configuration can be made up of 2 application software, 2 virtual machines and 20 firmwares. These can all overlap. (Many to Many relationship).

So I have created one table to record the "software configurations", and since this is a many-to-many relationship I have also created 4 bridging tables, one for each of the four software tables.

Overall I have 9 tables for this segment of my database.

However, when I want to create a new software configuration (with a small addition or subtraction) for example go from config 1.1.1 to config 2.0.0 I don't want to enter all of the related software again, as this would take huge amounts of time. Nor do I want to change the name of the previous version,
As I am keeping all the previous records for later reports.

My question:
How do I create a new configuration, while duplicating all of it's related records in each of the 4 bridging tables?

Here is an imgur link to a picture of the relationship diagram I have described, I'm not sure how to link an imagine here, sorry.



https://imgur.com/f01Xnw0

PS: This is the first time I'm posting on these forums,
sorry in advance if this is the incorrect place or wrong format etc,
please feel free to let me know the best practices for this space.

[June7]

Use the Advanced post editor to manage attachments - which include files of images as well as others such as Access db.

One approach is an INSERT SELECT action SQL. Review https://www.w3schools.com/SQL/sql_in...nto_select.asp

Another is to open and manipulate recordset objects.

However, this is usually used just to create record(s) in one table. Your requirement will get quite complicated because of need to save parent record first, get its primary key (are you using autonumber) to save with related records in multiple dependent tables. This is something I've had to program. It's harder with autonumber key and I've done it with and without.

[GuysImConfused]

Use the Advanced post editor to manage attachments - which include files of images as well as others such as Access db.

One approach is an INSERT SELECT action SQL. Review https://www.w3schools.com/SQL/sql_in...nto_select.asp

Another is to open and manipulate recordset objects.

However, this is usually used just to create record(s) in one table. Your requirement will get quite complicated because of need to save parent record first, get its primary key (are you using autonumber) to save with related records in dependent tables. This is something I've had to program. It's harder with autonumber key and I've done it with and without.

I checked out the website you linked, I'm not sure how the insert into statement helps me, since that is just copying one record from table A, into table B. Maybe I am missing something.

As for my parent table, yes I am using autonumber for the primary key.

[GuysImConfused]

Use the Advanced post editor to manage attachments - which include files of images as well as others such as Access db.

One approach is an INSERT SELECT action SQL. Review https://www.w3schools.com/SQL/sql_in...nto_select.asp

Another is to open and manipulate recordset objects.

However, this is usually used just to create record(s) in one table. Your requirement will get quite complicated because of need to save parent record first, get its primary key (are you using autonumber) to save with related records in dependent tables. This is something I've had to program. It's harder with autonumber key and I've done it with and without.

Thanks for the reply June7.

I had a look at that link you provided, and I am not sure how to utilize it to help my situation.

As for your question, I am using autonumber as the primary key for my main table.

[pbaldy]

Posts 3 and 4 were moderated, I'm posting to trigger email notifications.

[June7]

Here is simple example of pulling data from existing record(s) to create new record(s) with dynamic inputs:

CurrentDb.Execute "INSERT INTO OrderDetails(OrderID_FK, ProductID_FK) SELECT " & Me.tbxOrderID & ", ProductID_FK FROM OrderDetails WHERE OrderID_FK = " & Me.tbxOLDOrderID

In your case you would have to save parent record, retrieve the generated autonumber then run 4 INSERT SELECT actions to save related records with that ID as foreign key.

[pbaldy]

Allen's example may be helpful:

http://allenbrowne.com/ser-57.html

[CarlettoFed]

Perhaps it is easier to help you if you attach a file, even Excel that follows the same structure as the one in Access, with real data, replacing any sensitive data, in order to have a correct view of what you would like to achieve.

[GuysImConfused]

SIF Draft V2.1.4 (WIP) - Copy.zip

Here is simple example of pulling data from existing record(s) to create new record(s) with dynamic inputs:

CurrentDb.Execute "INSERT INTO OrderDetails(OrderID_FK, ProductID_FK) SELECT " & Me.tbxOrderID & ", ProductID_FK FROM OrderDetails WHERE OrderID_FK = " & Me.tbxOLDOrderID

In your case you would have to save parent record, retrieve the generated autonumber then run 4 INSERT SELECT actions to save related records with that ID as foreign key.

I just want to provide a bit more context. I am using forms in my database for data entry.

I have one form called DISPLAY_SoftwareConfigurations. This form has a combobox, which lets the user select a configuration from a list.
Once selected the rest of the fields on said form are populated with the related information.

I want to add a button to this form, which duplicates this software configuration.
In duplicating said configuration, the related records in the 4 bridging tables needs to be duplicated too.

This code which you have provided, would I place this inside this button, as an event -> on click?

PS: Attached is a copy of the database. Hopefully you have some time to take a look and provide feedback, I believe I have removed anything that may be confidential. It almost entirely contains filler data at this stage.

SIF Draft V2.1.4 (WIP) - Copy.zip

[GuysImConfused]

Allen's example may be helpful:

http://allenbrowne.com/ser-57.html

Thank you for providing this link, despite being confused how to correctly utilize the code, I was able to figure it out in the end.
I have solved my issue and I am able to duplicate my original record, as well as all the related records in the 4 junction tables.

The code I used is as follows:

Private Sub Button_Duplicate_Click() Dim strSql As String 'The SQL statement
Dim lngInt As Long 'Primary key value for the new duplicate record

'Following code saves any edits which might be underway already
If Me.Dirty Then
Me.Dirty = False
End If

'Following code makes sure there is a record available for duplication
If Me.NewRecord Then
MsgBox "Select the SW Configuration to duplicate first!"
Else
'Following code duplicates the main record
With Me.RecordsetClone
.AddNew
'ConfigCurrent will be duplicated: if you duplicate a current config, the dupe will be current
'If you duplicate a non-current config, the dupe will be non-current
!ConfigCurrent = Me.ConfigCurrent
!ConfigVersionNumber = Me.ConfigVersionNumber
!ConfigCreationDate = Date
!ConfigExpiryDate = Me.ConfigExpiryDate
!Changelog = Me.Changelog
!TPK_ID = Me.TPK_ID
!ScParentSystem = Me.ScParentSystem
.Update
'After duplicating the record, whether or not it was current, it will be set to non-current
Me.ConfigCurrent = False

'Save the primary key value, to use as the foreign key for the related records
.Bookmark = .LastModified
lngID = !SC_ID

'1: Duplicate the related records in Subform1 (Application Softwares): append query
If Me.Subform1.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [BridgeTbl_SwConfig_AppSw] (SwConfigId, AppSwId )" & _
"SELECT " & lngID & " As NewID, AppSwId " & _
"FROM [BridgeTbl_SwConfig_AppSw] WHERE SwConfigId = " & Me.SC_ID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related Application Softwares."
End If


'2: Duplicate the related records in Subform2 (Virtual Machines): append query
If Me.Subform2.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [BridgeTbl_SwConfig_VmSw] (SwConfigId, VmSwId )" & _
"SELECT " & lngID & " As NewID, VmSwId " & _
"FROM [BridgeTbl_SwConfig_VmSw] WHERE SwConfigId = " & Me.SC_ID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related Virtual Machines."
End If


'3: Duplicate the related records in Subform3 (Developed Codes): append query
If Me.Subform3.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [BridgeTbl_SwConfig_DevCode] (SwConfigId, DevCodeId )" & _
"SELECT " & lngID & " As NewID, DevCodeId " & _
"FROM [BridgeTbl_SwConfig_DevCode] WHERE SwConfigId = " & Me.SC_ID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related Developed Codes."
End If


'4: Duplicate the related records in Subform4 (Firmwares / Equipment Configurations): append query
If Me.Subform4.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [BridgeTbl_SwConfig_Equipment] (SwConfigId, EquipmentConfigId )" & _
"SELECT " & lngID & " As NewID, EquipmentConfigId " & _
"FROM [BridgeTbl_SwConfig_Equipment] WHERE EquipmentConfigId = " & Me.SC_ID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related Firmwares."
End If


'Display the new duplicate record
Me.Bookmark = .LastModified
End With
End If

End Sub

[pbaldy]

Happy to help!

[GuysImConfused]

Happy to help!

Actually, after some testing... It appears that only Subform1,2 & 3 successfully duplicate their information. Subform 4 does not duplicate for some reason

Can anybody tell what issue is in this code?
'4: Duplicate the related records in Subform4 (Firmwares / Equipment Configurations): append query
If Me.Subform4.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [BridgeTbl_SwConfig_Equipment] (SwConfigId, EquipmentConfigId )" & _
"SELECT " & lngID & " As NewID, EquipmentConfigId " & _
"FROM [BridgeTbl_SwConfig_Equipment] WHERE EquipmentConfigId = " & Me.SC_ID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related Firmwares."
End If

[pbaldy]

I'd start with this and see if the SQL looks correct:

http://www.baldyweb.com/ImmediateWindow.htm

I assume there are matching records.

[GuysImConfused]

I'd start with this and see if the SQL looks correct:

http://www.baldyweb.com/ImmediateWindow.htm

I assume there are matching records.

I tried this and it seems that the SQL statement is fine.

After some investigation I've discovered that my junction table seems to be locked somehow.

BridgeTbl_SwConfig_Equipment seems not to be editable. I can't figure out why.

[pbaldy]

Is it editable directly in the table? Are you adding all the required fields? If it's a linked SQL Server table, does it have a primary key set?