locked
Update a record with the contents of another record and its subform RRS feed

  • Question

  • I have an Access form containing all the fields I want to copy to another record.  I also want to copy its subform contents.  I have code that works for this from Allen Browne "Duplicate the record in a form and subform".  The problem is that I do not want to generate a new primary key number (I do not want to create a new record).  Instead, I want to use an existing record and update the contents of that record to my target record.  I want to identify the target record by its primary key, ShipmentID.   My code is below.  Where it says .AddNew, I want to find a record where ShipmentID = 12345 and copy there .

    I don't know what the option for RecordsetClone is to update an existing record.

    I also don't know how to pass the value of the target ShipmentID to the code. 

    Thank you!

    Here is the code:

    Private Sub cmdDupe_Click()
    'Source: http://allenbrowne.com
    'Adapted from: http://allenbrowne.com

    '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 !CustomerID = Me.CustomerID!EmployeeID = Me.EmployeeID!OrderDate = Date 'etc for other fields. .Update 'Save the primary key value, to use as the foreign key for the related records. .Bookmark = .LastModified lngID = !ShipmentID 'Duplicate the related records: append query. If Me.[Orders Subform].Form.RecordsetClone.RecordCount > 0 Then strSql = "INSERT INTO [Order Details] ( ShipmentID, ProductID, Quantity, UnitPrice, Discount ) " & _ "SELECT " & lngID & " As NewID, ProductID, Quantity, UnitPrice, Discount " & _ "FROM [Order Details] WHERE OrderID = " & Me.OrderID & ";" 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: MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click" Resume Exit_Handler End Sub

    Sunday, September 11, 2016 5:17 PM

Answers

  • Hi SammieKay,

    According to your description, you may want to create a recordsetclone to find a record in a subform, and move the form to the record found. You could find the first record with a matching ShipmentID, then move to that record if one is found.

    For more information, click here to refer about Using a RecordsetClone with Microsoft Access Subforms

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Thanks for your understanding.
    • Marked as answer by SammieKay Wednesday, September 14, 2016 1:13 PM
    Wednesday, September 14, 2016 1:45 AM

All replies

  • Hi,

    I notice that your issue is code related for Access. To better resolve your issue, I will move your thread to Access for Developers forum for further help. Hope you can find the solution there :)


    Regards,

    Winnie Liang


    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, September 12, 2016 6:18 AM
  • Hi SammieKay,

    According to your description, you may want to create a recordsetclone to find a record in a subform, and move the form to the record found. You could find the first record with a matching ShipmentID, then move to that record if one is found.

    For more information, click here to refer about Using a RecordsetClone with Microsoft Access Subforms

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Thanks for your understanding.
    • Marked as answer by SammieKay Wednesday, September 14, 2016 1:13 PM
    Wednesday, September 14, 2016 1:45 AM