Duplicate Order


New member
Aug 1, 2017
Programming Experience
Hello, I created a MS Access Application and decided to learn some VB and make it an application. I been able to recreate it but i am now stuck on the last feature.
I have an order edit form where the master record is on the form and a subform shows the details. There is a Re-order checkbox on the subform that can be checked and when finished a button click creates a new master with the details containing the items that were checked in the subform and this is now displayed in the order edit form. I use the code below to accomplish this. I use a dateset created with dataset designer and a datagridview for the subform. I'm new to vb with little experience. Thanks for any help
the vba code

Private Sub cmdReOrder1_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."
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
                !EmpID = Me.cboEmpID
                !Vendor = Me.cboVendor
                !CustomerId = Me.cboPhone
                !OrderDate = Date
                'etc for other fields.
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !ID
            'Duplicate the related records: append query.
            If Me.[frmOrderEditSub].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [dbo_tblOrderDetails] ( OrderNumber, AceNumber, Quantity, Price ) " & _
                 "SELECT " & lngID & " As NewID, AceNumber, Quantity, Price " & _
                    "FROM [dbo_tblOrderDetails] WHERE Ordernumber = " & Me.ID & " And ReOrder = 1;"
                DBEngine(0)(0).Execute strSql, dbFailOnError
                  MsgBox "New Order Complete, Edit and Save", vbOKOnly + vbInformationc
                  Me.lblNew.Visible = True
                 ' Forms!frmOrderEdit!frmOrderEditSub.Form!chkreorder.Visible = False
                  'Forms!frmOrderEdit!frmOrderEditSub.Form!label119.Visible = False
                MsgBox "Main record duplicated, but there were no related records."
            End If
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

    Exit Sub

    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
End Sub
Top Bottom