Order genereren vanuit een offerte

    strSQL = "SELECT OfferteID, Item, Qty, Unitprice, Discount, Total, Artikeltekst, ArtikelID, [Description /partnumber], Artikelnummer FROM [Offerte-inhoud] " _
    & "WHERE OfferteID=" & Me.OfferID
    Set rst = CurrentDb.OpenRecordset(strSQL)
    Set rso = CurrentDb.OpenRecordset("Order-inhoud")
    If Not rst.RecordCount = 0 Then
        Do While Not rst.EOF
            With rso
                .AddNew
                !OrderID = iOrderID
                !Item = rst!Item
                !Qty = rst!Qty
                !Unitprice = rst!Unitprice
                !Discount = rst!Discount
                !Offertenummer = rst!OfferteID
                !Artikeltekst = rst!Artikeltekst
                !ArtikelID = rst!ArtikelID
                !Artikelnummer = rst!Artikelnummer
                tmp = rst.Fields(9).Name
                tmp = rst.Fields(8).Name
                .Fields(4) = rst.Fields(8)
                !OrderID = iOrderID
                .Update
            End With
            rst.MoveNext
        Loop
        rst.Close
    End If

If lngInkooporderHeader = -1 Then Err.Raise 64000, , "No active Order found with orderID = " & lngOrderID

Public Sub sCopyOrderToInkooporder(lngOrderID As Long)
On Error GoTo Err_sCopyOrderToInkooporder:

    Dim lngInkooporderHeader As Long
    Dim rstLeveranciers As New ADODB.Recordset
    Dim cmdAddInkooporderDetails As New ADODB.Command
    Dim lngCountDetails As Long, lngAlldetails As Long, intCountOrders As Integer
    Dim strSQL As String
    
    strSQL = "SELECT  artikelen.LeverancierID FROM artikelen INNER JOIN [Order-inhoud] ON artikelen.ArtikelID = [Order-inhoud].ArtikelID " & _
                    " where orderID = " & lngOrderID & " GROUP BY [Order-inhoud].OrderID, artikelen.LeverancierID;"
    rstLeveranciers.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    With rstLeveranciers
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Do While .EOF = False
                lngInkooporderHeader = fCopyInkooporderHeader(lngOrderID, !LeverancierId)
                If lngInkooporderHeader = -1 Then Err.Raise 64000, , "No active Order found with orderID = " & lngOrderID
                intCountOrders = intCountOrders + 1
               
                cmdAddInkooporderDetails.ActiveConnection = CurrentProject.Connection
                cmdAddInkooporderDetails.CommandType = adCmdText
                cmdAddInkooporderDetails.CommandText = "INSERT INTO [Inkooporder-inhoud] ( InkooporderID,  Item, Qty, Unitprice, Discount, Total, Offertenummer, Artikeltekst, ArtikelID, Artikelnummer, Partnumber ) " & _
                        " select " & lngInkooporderHeader & " , Item, Qty, Unitprice, Discount, Total, Offertenummer, Artikeltekst, [Order-inhoud].ArtikelID," & _
                        " [Order-inhoud].Artikelnummer, [Order-inhoud].Partnumber " & _
                        " from  [Order-inhoud]  INNER JOIN artikelen ON [Order-inhoud].ArtikelID = Artikelen.ArtikelID " & _
                        " where OrderID = " & lngOrderID & " and LeverancierID = " & !LeverancierId
                Debug.Print cmdAddInkooporderDetails.CommandText
                cmdAddInkooporderDetails.Execute lngCountDetails
                lngAlldetails = lngAlldetails + lngCountDetails
                .MoveNext
            Loop
        End If
        .Close
    End With
   
    
    MsgBox intCountOrders & "Order(s) created with " & lngAlldetails & " order lines"
        
Exit_sCopyOrderToInkooporder:
    Set cmdAddInkooporderDetails = Nothing
    Set rstLeveranciers = Nothing
    Exit Sub
    
Err_sCopyOrderToInkooporder:
    MsgBox "Error " & Err.Number & " - " & Err.Description & " in method Copy order to Inkooporder " & vbLf & "Please contact your application administrator."
    Resume Exit_sCopyOrderToInkooporder

End Sub
Private Function fCopyInkooporderHeader(lngOrder, lngLeverancier) As Long
On Error GoTo Err_fCopyInkooporderHeader

    Dim rstOrder As New ADODB.Recordset
    Dim rstInkooporder As New ADODB.Recordset
    Dim lngInkooporderID As Long
   
    rstOrder.Open "select * from Orders where OrderID = " & lngOrder, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    With rstOrder
        If .BOF And .EOF Then Err.Raise 64000
        rstInkooporder.Open "select * from Inkooporders where 1=0", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
        .MoveFirst
        rstInkooporder.AddNew
        rstInkooporder!OrderDatum = Date
        rstInkooporder!Status = "Lopend"
        rstInkooporder!Leverancier_ID = lngLeverancier
        rstInkooporder.Update
        lngInkooporderID = rstInkooporder!InkooporderID
        rstInkooporder.Close
        .Close
    End With
    fCopyInkooporderHeader = lngInkooporderID
        

Exit_fCopyinkooporderHeader:
    Set rstOrder = Nothing
    Set rstOrder = Nothing
    Exit Function
    
Err_fCopyInkooporderHeader:
Debug.Print Err.Number & Err.Description
    fCopyInkooporderHeader = -1
    Resume Exit_fCopyinkooporderHeader
    
End Function