Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Duplicate the record in form and subform - ACCESS 2013

    I have been picking away and searching dbforums for advice and insight on this for a couple of weeks...I have finally come to a workable solution that I am stuck on

    Thank you for reading!

    I have an orders/order details database and invoices that are uploaded from an outside order system through excel.
    I wrote a series of queries to match the invoices to the orders and determine if it is (still open)backordered. Then I isolate my backorder and my backorder details into make table query

    Using Allen Browne example in Northwinds, I need to duplicate the back order/back order details into the orders/order details tables (with new ID's - for the next invoice).
    I want to have a back order form/back order details subform providing the records to duplicate, with record sources from my make tables called mtBO, mtBODetails. With the OnClick VBA, I want the duplcates to save in my orders/order details tables
    I am trying to adapt his vba with this variation........but I'm having trouble making the loop.

    I attached tables - mtBO, 10 records (main form record source) and mtBODetails, 20 records (subform record source, frmsAMZBO) are true records of the first back orders I am returning to the tblAOpenOrder and tblAOpenOrderdetails
    You will see the mtBO on page 5 of the tblAOpenOrder table. Each of those lines needs to be "duplicated" with the mtBO values under a new AOOID in tblAOpenOrder
    The mtBODetails are the foreign key matches to the new AOOID to be saved under new AODID in tblAOpenOrderdetails


    1.) How would I adapt duplicating the main record in recordsetClone to save to a table that is not the record source of the form, mtBO ?

    2.) To duplicate the related records in an append query, do I name a blank query and VBA does the rest? Are the INSERT INTO and FROM functions referring to the append query name or the table ? In this code, I don't think I have looped the tables correctly, and I haven't even referenced an append query - I haven't created it yet.

    mtBO.pdfmtBODetails.pdftblAOpenOrderdetails.pdftblAOpenOrder.pdf
    Code:
    Private Sub Command7_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
                    !NENO = Me.NENO
                    !CUSTPO = Me.CUSTPO
                    !BO = Me.BO
                    !ORDERDATE = Date
                    !INVNOTE = Me.INVNOTE
                    !DROPSHIP = Me.DROPSHIP
                    !DELCANCEL = Me.DELCANCEL
                    'etc for other fields.
                .UPDATE
                
                'Save the primary key value, to use as the foreign key for the related records.
                .Bookmark = .LastModified
                lngID = !AOOID
                
                'Duplicate the related records: append query.
                If Me.[frmsAMZBO].Form.RecordsetClone.RecordCount > 0 Then
                    strSql = "INSERT INTO [tblAOpenOrderdetails] ( AOOID, AODID, APID, PARTNO, QTYORDERED ) " & _
                        "SELECT " & lngID & " As NewID, AODID, APID, PARTNO, QTYORDERED " & _
                        "FROM [tblAOpenOrder] WHERE AOOID = " & Me.AOOID & ";"
                    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, , "Command7_Click"
        Resume Exit_Handler
    End Sub

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    For 99% of access, you don't need to write code.
    Data is added to a table in a form. No code.
    You can move data in queries. No code.

    Same with your need above. Use a query to pull data from 1 table and put in another.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why are you duplicating data in the first palce?

    if an item is on back order, its on order. just hasn't arrived as yet, or there is no predicted arrival date
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •