Solved Copying data from Quote-line-items Subform to Sales-Order-line-items Table : Error 3022 (1 Viewer)

[alan2013]

In my database, I have a 'Quote' form with a Quote-line-items subform (sfrmQuoteLineItems) and a 'Sales Order Form' with an 'Order line items' subform (sfrmOrderLineItems)

In the header of sfrmQuoteLineItems, I've placed an unbound field (unbQuoteLineItemsRecordCount) to provide a count of the number of quote line items . ie. the Control Source is set to '=Count(*)'.

On the 'Quote' form (frmQuoteDetails), I have a button for allowing me - after a Quote has been accepted - to copy Quote-line-items data from the subform to the Order details Table (tblOrderDetails).

Currently, the VBA code relating to the copying-over is as follows :

           Dim rst_quotelineitems As DAO.Recordset
           Set rst_quotelineitems = Me.sfrmQuoteLineItems.Form.RecordsetClone
                      
           Dim productID As String
           Dim orderdetailstatusID As String
           Dim quantity As Integer
           Dim price As Currency
          
           Dim quotelineitemcount As Integer
           quotelineitemcount = Me!sfrmQuoteLineItems.Form!unbQuoteLineItemsRecordCount.Value
                                
           Dim rst_orderlineitems As DAO.Recordset
           Set rst_orderlineitems = CurrentDb.OpenRecordset("tblOrderDetails", dbOpenTable)
          
           Dim counter As Integer
           counter = 0
           rst_quotelineitems.MoveFirst         

           Do While counter < quotelineitemcount
              counter = counter + 1
              With rst_orderlineitems
                 .AddNew
                 productID = Me!sfrmQuoteLineItems.Form!intProductID_FK.Value
                 orderdetailstatusID = Me!sfrmQuoteLineItems.Form!intQuoteDetailStatusID_FK.Value
                 quantity = Me!sfrmQuoteLineItems.Form!intQuantity.Value
                 price = Me!sfrmQuoteLineItems.Form!curUnitPrice.Value

                 !intOrderID_FK = neworderID
                 !intProductID_FK = productID
                 !intQuantity = quantity
                 !curUnitPrice = price
                 !intOrderDetailStatusID_FK = orderdetailstatusID
                 .Update
             End With           
             rst_quotelineitems.MoveNext
           Loop
          
           rst_quotelineitems.Close
           Set rst_quotelineitems = Nothing

The problem I'm encountering is :
In testing this, I have a sfrmQuoteLineItems subform with two records.
On clicking the button, one record is created successfully in tblOrderDetails. But then I get an Error 3022, pointing to the '.Update' line. The details relating to the second of the two quote-line-items are NOT copied across to tblOrderDetails.

Can anyone provide me with some guidance on this, please ? Thanks in advance.

 

[arnelgp]

the problem with the code is you are using the Controls on the subform rather than using the Fields on the recordsetClone.

    Dim rst_quotelineitems As DAO.Recordset
    Set rst_quotelineitems = Me.sfrmQuoteLineItems.Form.RecordsetClone

    Dim productID As String
    Dim orderdetailstatusID As String
    Dim quantity As Integer
    Dim price As Currency

    Dim rst_orderlineitems As DAO.Recordset
    Set rst_orderlineitems = CurrentDb.OpenRecordset("tblOrderDetails", dbOpenTable)

    With rst_quotelineitems

        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
       
        Do Until .EOF
                productID = !intProductID_FK & ""
                orderdetailstatusID = !intQuoteDetailStatusID_FK & ""
                quantity = Val(!intQuantity & "")
                price = Val(!curUnitPrice & "")
               
            With rst_orderlineitems
                .AddNew
                !intOrderID_FK = neworderID
                !intProductID_FK = productID
                !intQuantity = quantity
                !curUnitPrice = price
                !intOrderDetailStatusID_FK = orderdetailstatusID
                .Update
            End With
           
            .MoveNext
        Loop
    End With

    rst_orderlineitems.Close
    Set rst_orderlineitems = Nothing
    Set rst_quotelineitems = Nothing

 

[CJ_London]

What is the description for the 3022 error?

 

[arnelgp]

so, it would create a duplicate index, therefore just ignore the error since you already have those records on your table.

 

[alan2013]

What is the description for the 3022 error?

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

arnelgp seems to have provided code that resolves the issue.
In any case, Thanks.

​

 

[alan2013]

so, it would create a duplicate index, therefore just ignore the error since you already have those records on your table.

I think I'd been a bit careless in cutting-n-pasting code around.
The code you provided certainly seems to have worked. Thanks ever so much !

 

[Pat Hartman]

You don't need all that code. You just need to run an append query with two arguments - From QuoteFK value for selection and To OrderFK value to attach the copied records to the order