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 :
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.
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 :
Code:
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.