Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 09-07-2013, 05:49 PM   #1
Harry Paraskeva
Newly Registered User
 
Join Date: Sep 2013
Posts: 33
Thanks: 8
Thanked 1 Time in 1 Post
Harry Paraskeva is on a distinguished road
Duplicate Button for Form/Subform/Subform

I have a quite difficult problem to solve in Access and I hope someone can help. I have been struggling with it for a week now. My programming abilities are mediocre, but not novice.

I have a pretty large scale management system to build (more than 40 tables, lots of relationships, forms, etc). One of the structures in the database is the following:
+ Main table
++ Secondary table connected to Main table via FK1.
+++Tertiary table connected to Secondary table via FK2.

The above when making the user interface is expressed as a form (Main Table), a subform (Secondary table) and a subform within the subform (Tertiary table).

I need to create a button that duplicates the contents of the main table, the secondary table and the tertiary table. I have already been able to create a button that duplicates the contents of the main table and the secondary table by using Allen Browne's code found online.

The question is how do I move forward to embedding in that method the ability to duplicate the contents of the Tertiary Table? Any ideas?

Harry Paraskeva is offline   Reply With Quote
Old 09-07-2013, 05:56 PM   #2
Harry Paraskeva
Newly Registered User
 
Join Date: Sep 2013
Posts: 33
Thanks: 8
Thanked 1 Time in 1 Post
Harry Paraskeva is on a distinguished road
Re: Duplicate Button for Form/Subform/Subform

In case anyone is wondering what type of VB code I've used, I'm pasting a sample of it (the first 5 of 16 subforms the data of which get duplicated this way)

Code:
Private Sub Command3260_Click()

On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSQL As String    'SQL statement.
    Dim strSQL2 As String
    Dim strSQL3 As String
    Dim strSQL4 As String
    Dim strSQL5 As String
    Dim lngID As Long       'Primary key value of the new record.
    
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !ContextID = Me.ContextID
                ![Material Type] = Me.[Material Type]
                ![Ware Type] = Me.[Ware Type]
                ![Ware Certainty] = Me.Ware_Certainty
                ![Ware Sub-Type] = Me.Ware_Sub_Type
                ![Shape General Type] = Me.[Shape General Type]
                ![Shape Specific Type] = Me.[Shape Specific Type]
                ![Shape Certainty] = Me.Shape_Certainty
                ![Preservation State] = Me.[Preservation State]
                ![Preservation Quality] = Me.[Preservation Quality]
                ![Forming Notes] = Me.Forming_Notes
                ![Finishing Notes] = Me.Finishing_Notes
                ![Firing Conditions] = Me.[Firing Conditions]
                ![Fabric Visible] = Me.Fabric_Visible
                ![Fabric Type] = Me.[Fabric Type]
                ![Fabric Texture] = Me.[Fabric Texture]
                ![Fabric Hardness] = Me.[Fabric Hardness]
                ![Fabric Fracture] = Me.[Fabric Fracture]
                ![Fabric Core] = Me.[Fabric Core]
                ![Fabric Core Colour] = Me.[Fabric Core Colour]
                ![Notes] = Me.Notes
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !SherdID
                        
            'Duplicate the related records: append query.
            If Me.DiagnosticParts_subform.Form.RecordsetClone.RecordCount > 0 Or _
            Me.FunctionTypes_subform.Form.RecordsetClone.RecordCount > 0 Or _
            Me.FormingTechniques_subform.Form.RecordsetClone.RecordCount > 0 Or _
            Me.FinishingTechniques_subform.Form.RecordsetClone.RecordCount > 0 Or _
            Me.DiagnosticsSurfacesubform.Form.RecordsetClone.RecordCount > 0 Then
             If Me.DiagnosticParts_subform.Form.RecordsetClone.RecordCount > 0 Then
                strSQL = "INSERT INTO [DiagnosticParts] (ID, SherdID, Body, Rim, Neck, Shoulder, Base, HandleLug, Spout ) " & _
                    "SELECT " & DMax("ID", "DiagnosticParts") + 1 & " As NewID, " & lngID & " As NewSherdID, Body, Rim, Neck, Shoulder, Base, HandleLug, Spout " & _
                    "FROM [DiagnosticParts] WHERE SherdID = " & Me.SherdID & ";"
               DBEngine(0)(0).Execute strSQL, dbFailOnError
            End If
            If Me.FunctionTypes_subform.Form.RecordsetClone.RecordCount > 0 Then
               strSQL2 = "INSERT INTO [FunctionTypes] (ID, SherdID, [Liquid Storage], [Dry Storage], [Food PreparationService], Cooking, RitualCeremonial) " & _
                    "SELECT " & DMax("ID", "FunctionTypes") + 1 & " As NewID, " & lngID & " As NewSherdID, [Liquid Storage], [Dry Storage], [Food PreparationService], Cooking, RitualCeremonial " & _
                    "FROM [FunctionTypes] where SherdID = " & Me.SherdID & ";"
               DBEngine(0)(0).Execute strSQL2, dbFailOnError
            End If
            If Me.FormingTechniques_subform.Form.RecordsetClone.RecordCount > 0 Then
               strSQL3 = "INSERT INTO [FormingTechniques] (ID, SherdID, PinchingDrawing, [CoilingSlab Building], MoldingPressing, Throwing, Unknown) " & _
                    "SELECT " & DMax("ID", "FormingTechniques") + 1 & " As NewID, " & lngID & " As NewSherdID, PinchingDrawing, [CoilingSlab Building], MoldingPressing, Throwing, Unknown " & _
                    "FROM [FormingTechniques] WHERE SherdID = " & Me.SherdID & ";"
               DBEngine(0)(0).Execute strSQL3, dbFailOnError
            End If
            If Me.FinishingTechniques_subform.Form.RecordsetClone.RecordCount > 0 Then
               strSQL4 = "INSERT INTO [FinishingTechniques] (ID, SherdID, BeatingPaddling, Scraping, TrimmingFettling, Smoothing, BurnishingPolishing, Roughening, PatterningTexturing, Unknown) " & _
                    "SELECT " & DMax("ID", "FinishingTechniques") + 1 & " As NewID, " & lngID & " As NewSherdID, BeatingPaddling, Scraping, TrimmingFettling, Smoothing, BurnishingPolishing, Roughening, PatterningTexturing, Unknown " & _
                    "FROM [FinishingTechniques] WHERE SherdID = " & Me.SherdID & ";"
               DBEngine(0)(0).Execute strSQL4, dbFailOnError
            End If
            If Me.DiagnosticsSurfacesubform.Form.RecordsetClone.RecordCount > 0 Then
               strSQL5 = "INSERT INTO [DiagnosticsSurface] (ID, SherdID, [Incised Motifs], [Relief Motifs], [Impressed Motifs], [Painted Motifs], [Burnished Motifs], [Slipping Inside], [Slipping Outside], [Slipping Preservation Inside]," _
               & "[Slipping Preservation Outside], [Slipping Inside Type], [Slipping Outside Type], [Painting Inside], [Painting Outside], [Painting Preservation Inside], [Painting Preservation Outside], [Painting Thickness], [Incising Inside]," _
               & "[Incising Outside], [Incising Preservation Inside], [Incising Preservation Outside], [Incising Type], [Burnishing Inside], [Burnishing Outside], [Burnishing Preservation Inside], [Burnishing Preservation Outside], [Burnishing Type]," _
               & "[Combing Inside], [Combing Outside], [Combing Preservation Inside], [Combing Preservation Outside], [ImpressingStamping Inside], [ImpressingStamping Outside], [ImpressingStamping Preservation Inside], [ImpressingStamping Preservation Outside]," _
               & "[AppliqueRelief Inside], [AppliqueRelief Outside], [AppliqueRelief Preservation Inside], [AppliqueRelief Preservation Outside], [Lustre Inside], [Lustre Outside])" & _
                    "SELECT " & DMax("ID", "DiagnosticsSurface") + 1 & " As NewID, " & lngID & " As NewSherdID, [Incised Motifs], [Relief Motifs], [Impressed Motifs], [Painted Motifs], [Burnished Motifs], [Slipping Inside], [Slipping Outside], [Slipping Preservation Inside]," _
               & "[Slipping Preservation Outside], [Slipping Inside Type], [Slipping Outside Type], [Painting Inside], [Painting Outside], [Painting Preservation Inside], [Painting Preservation Outside], [Painting Thickness], [Incising Inside]," _
               & "[Incising Outside], [Incising Preservation Inside], [Incising Preservation Outside], [Incising Type], [Burnishing Inside], [Burnishing Outside], [Burnishing Preservation Inside], [Burnishing Preservation Outside], [Burnishing Type]," _
               & "[Combing Inside], [Combing Outside], [Combing Preservation Inside], [Combing Preservation Outside], [ImpressingStamping Inside], [ImpressingStamping Outside], [ImpressingStamping Preservation Inside], [ImpressingStamping Preservation Outside]," _
               & "[AppliqueRelief Inside], [AppliqueRelief Outside], [AppliqueRelief Preservation Inside], [AppliqueRelief Preservation Outside], [Lustre Inside], [Lustre Outside]" & _
                    "FROM [DiagnosticsSurface] WHERE SherdID = " & Me.SherdID & ";"
                DBEngine(0)(0).Execute strSQL5, dbFailOnError
            End If
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
    
End Sub
Harry Paraskeva is offline   Reply With Quote
Old 09-09-2013, 03:58 AM   #3
Harry Paraskeva
Newly Registered User
 
Join Date: Sep 2013
Posts: 33
Thanks: 8
Thanked 1 Time in 1 Post
Harry Paraskeva is on a distinguished road
Re: Duplicate Button for Form/Subform/Subform

After doing some research on my own, I still couldn't find any help and I tend to believe that it is not possible to be done. So if you are designing large scale systems avoid going beyond the first level of connecting things if you want to retain the duplicate/copy-paste ability. I had to go over my db design and redraw some tables in order to do that and that got things working for me.

Harry Paraskeva is offline   Reply With Quote
Reply

Tags
button , duplicate , form , subform

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Duplicate Form/SubForm/Subform within subform Grooz13 Modules & VBA 0 02-28-2012 06:40 AM
Duplicate the record in form and subform Sarella Modules & VBA 4 01-11-2011 02:11 PM
Duplicate Form and Subform Data fenhow Modules & VBA 2 11-04-2009 01:27 PM
Button on form enabling a subform inside of a subform. BPHusker Modules & VBA 3 04-16-2008 08:47 PM
Duplicate a form and subform using an autonumber paul young Modules & VBA 2 06-04-2006 01:22 AM




All times are GMT -8. The time now is 05:32 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World