Results 1 to 4 of 4
  1. #1
    Noodle1791 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Location
    Harlow
    Posts
    5

    Copying records on a subform (Almost there!)

    Good morning everyone,

    I'm hoping that someone can shed some light on a frustrating problem for which I cannot find a solution.

    I have a form and a series of sub forms which form a test record database. The end user has a duplicate button available to them which when clicked copies the main form details as required then the sub forms data. I used code from Allen Browne (and adapted for use) which works really well on all but one of the subforms called [VisualCheckTickF]. Now I cannot see what the problem is although the sub form in question stores yes/no records. Could this be the problem? Have I typed something incorrect that I just cant see?

    I am not an Access expert by a long shot so I am looking for some kind pointers.

    Here is the code on the 'click' event (The VisualCheckTickF part is towards the bottom);

    Private Sub Duplicate_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 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
    !ItemNumber = Me.ItemNumber
    !Description = Me.Description
    !ApplicableTIS = Me.ApplicableTIS
    !Note = Me.Note
    !TestingStarted = Date
    !VisualInspectionNote = Me.VisualInspectionNote
    !ElectricalTestNote = Me.ElectricalTestNote
    !SpecialInspectionNote = Me.SpecialInspectionNote
    !EarthLeakageNote = Me.EarthLeakageNote
    !TransformerNote = Me.TransformerNote


    !ECMNote = Me.ECMNote
    'etc for other fields.
    .Update

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

    'Duplicate the related records: append query.
    If Me.[SpecialVisualCheckF].Form.RecordsetClone.RecordCount > 0 Then
    strSql = "INSERT INTO [SpecialVisualTitleT] ( CustomerID, InspectionTitle1, InspectionTitle2, InspectionTitle3, InspectionTitle4, InspectionTitle5, InspectionTitle6, InspectionTitle7, InspectionTitle8, InspectionTitle9, InspectionTitle10, InspectionTitle11, InspectionTitle12, InspectionTitle13, InspectionTitle14, InspectionTitle15 ) " & _
    "SELECT " & lngID & " As NewID, InspectionTitle1, InspectionTitle2, InspectionTitle3, InspectionTitle4, InspectionTitle5, InspectionTitle6, InspectionTitle7, InspectionTitle8, InspectionTitle9, InspectionTitle10, InspectionTitle11, InspectionTitle12, InspectionTitle13, InspectionTitle14, InspectionTitle15 " & _
    "FROM [SpecialVisualTitleT] WHERE CustomerID = " & Me.CustomerID & ";"
    DBEngine(0)(0).Execute strSql, dbFailOnError
    Else
    MsgBox "Main record duplicated, but there were no related records."
    End If

    'Duplicate the related records: append query.
    If Me.[TestSettingF].Form.RecordsetClone.RecordCount > 0 Then
    strSql = "INSERT INTO [TestSettingT] ( CustomerID, Flash, InsulationV, InsulationGreater, TxFlash, TxInsulation, TxLowestInsulation ) " & _
    "SELECT " & lngID & " As NewID, Flash, InsulationV, InsulationGreater, TxFlash, TxInsulation, TxLowestInsulation " & _
    "FROM [TestSettingT] WHERE CustomerID = " & Me.CustomerID & ";"
    DBEngine(0)(0).Execute strSql, dbFailOnError
    Else
    MsgBox "Main record duplicated, but there were no related records."
    End If

    'Duplicate the related records: append query.
    If Me.[CircuitF].Form.RecordsetClone.RecordCount > 0 Then
    strSql = "INSERT INTO [CircuitT] ( CustomerID, From1, To1, CircuitRating1, Cable1 ) " & _
    "SELECT " & lngID & " As NewID, From1, To1, CircuitRating1, Cable1 " & _
    "FROM [CircuitT] WHERE CustomerID = " & Me.CustomerID & ";"
    DBEngine(0)(0).Execute strSql, dbFailOnError
    Else
    MsgBox "Main record duplicated, but there were no related records."
    End If

    'Duplicate the related records: append query.
    If Me.[TransformerF].Form.RecordsetClone.RecordCount > 0 Then
    strSql = "INSERT INTO [TransformerT] ( CustomerID, TransformerFitted, UnitNumber, Rating, Phase, Vector, PrimaryVolts,SecondaryRating, SecVoltsNom, TertiaryRating, TerVoltsNom ) " & _
    "SELECT " & lngID & " As NewID, TransformerFitted, UnitNumber, Rating, Phase, Vector, PrimaryVolts,SecondaryRating, SecVoltsNom, TertiaryRating, TerVoltsNom " & _
    "FROM [TransformerT] WHERE CustomerID = " & Me.CustomerID & ";"
    DBEngine(0)(0).Execute strSql, dbFailOnError
    Else
    MsgBox "Main record duplicated, but there were no related records."
    End If

    'Duplicate the related records: append query.
    If Me.[VisualCheckTickF].Form.RecordsetClone.RecordCount > 0 Then
    strSql = "INSERT INTO [VisualCheckTickT] ( CustomerID, UnitClean, UnitClean1, Paintwork, Paintwork1, Label, Label1, Instruction, Instruction1, Seal, Seal1, Clearance, Clearance1, Specification, Specification1, Manual, Manual1, WiringDiagram, WiringDiagram1, RatingLabel, RatingLabel1, Busbar, Busbar1, Lifting, Lifting1, Fixing, Fixing1, Termination, Termination1, Markings, Markings1, Routing, Routing1, Sharp, Sharp1, Shrouds, Shrouds1 ) " & _
    "SELECT " & lngID & " As NewID, UnitClean, UnitClean1, Paintwork, Paintwork1, Label, Label1, Instruction, Instruction1, Seal, Seal1, Clearance, Clearance1, Specification, Specification1, Manual, Manual1, WiringDiagram, WiringDiagram1, RatingLabel, RatingLabel1, Busbar, Busbar1, Lifting, Lifting1, Fixing, Fixing1, Termination, Termination1, Markings, Markings1, Routing, Routing1, Sharp, Sharp1, Shrouds, Shrouds1 " & _
    "FROM [VisualCheckTickT] WHERE CustomerID = " & Me.CustomerID & ";"
    DBEngine(0)(0).Execute strSql, dbFailOnError
    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:

    Resume Exit_Handler

    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't see anything offhand. Does it error? This debugging technique may help:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Noodle1791 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Location
    Harlow
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    I don't see anything offhand. Does it error? This debugging technique may help:

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

    Thanks for the response back. Its not throwing up any errors. Ill take a look at debugging techniques.

    Thanks again

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. Post back if you're still stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Copying records from one table to another
    By willster88 in forum Access
    Replies: 2
    Last Post: 03-22-2014, 01:57 PM
  2. Replies: 2
    Last Post: 01-02-2014, 03:05 PM
  3. Copying info from one subform to another form
    By Stu Man Do in forum Forms
    Replies: 1
    Last Post: 04-03-2013, 04:27 PM
  4. Copying records
    By Lowell in forum Access
    Replies: 3
    Last Post: 09-07-2012, 12:26 AM
  5. copying data from subform to mainform
    By wasim_sono in forum Forms
    Replies: 0
    Last Post: 03-10-2006, 04:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums