I've caused myself an issue. Because I have unlocked the form ( thats ok ) and I am editing an "Invoiced" record ( thats ok ), my invoice code needs changing so the RST is updated and not created as a new line.
I had a quick meddle with the code but it over writes other invoice lines instead.
This is the before code which adds a new invoice line in tblInvoice:
Code: Select all
Case "Invoice"
If MsgBox("You Are About To Invoice This File, Do You Want To Continue?", vbYesNo, "Invoice") = vbYes Then
Set RST = DB.OpenRecordset("tblInvoice")
If DCount("*", "tblInvoice", "InvoiceNo = " & Me.txtInvoiceNumber.Value) > 0 Then
MsgBox "This File Has Already Been Invoiced!", vbExclamation
End If
RST.AddNew
RST!SalesOrderID = Forms!frmSales!SalesOrderID
RST!VinylRetail = Forms!frmSales!txtVinylTotal
RST!VinylGP£ = Forms!frmSales!txtVinylMarkup
RST!LabourRetail = Forms!frmSales!txtLabourTotal
RST!OtherRetail = Forms!frmSales!txtOtherTotal
RST!Postage = Forms!frmSales!txtPostage
RST!DiscountedSubTotal = Forms!frmSales!txtDiscountedTotal
RST!MarkupP = Forms!frmSales!Markup
RST!DiscountP = Forms!frmSales!txtDiscount
RST!Discount£ = Forms!frmSales!txtDiscTotal
RST!VAT£ = Forms!frmSales!txtVatAmount
RST!VATP = Forms!frmSales!VAT1
RST!TotalInCVAT = Forms!frmSales!TotalInCVAT
RST.Update
Set RST = Nothing
Set DB = Nothing
Me.Refresh
Else
End If
The code above needs to edit-update the existing invoice line, which it doesn't. You'll see I added the RST.Edit section which is wrong.
Code: Select all
Case "Invoice"
If MsgBox("You Are About To Invoice This File, Do You Want To Continue?", vbYesNo, "Invoice") = vbYes Then
Set RST = DB.OpenRecordset("tblInvoice")
If DCount("*", "tblInvoice", "InvoiceNo = " & Me.txtInvoiceNumber.Value) > 0 Then
MsgBox "This File Has Already Been Invoiced!", vbExclamation
RST.Edit
RST!SalesOrderID = Forms!frmSales!SalesOrderID
RST!VinylRetail = Forms!frmSales!txtVinylTotal
RST!VinylGP£ = Forms!frmSales!txtVinylMarkup
RST!LabourRetail = Forms!frmSales!txtLabourTotal
RST!OtherRetail = Forms!frmSales!txtOtherTotal
RST!Postage = Forms!frmSales!txtPostage
RST!DiscountedSubTotal = Forms!frmSales!txtDiscountedTotal
RST!MarkupP = Forms!frmSales!Markup
RST!DiscountP = Forms!frmSales!txtDiscount
RST!Discount£ = Forms!frmSales!txtDiscTotal
RST!VAT£ = Forms!frmSales!txtVatAmount
RST!VATP = Forms!frmSales!VAT1
RST!TotalInCVAT = Forms!frmSales!TotalInCVAT
RST.Update
Set RST = Nothing
Set DB = Nothing
Me.Refresh
Exit Sub
End If
RST.AddNew
RST!SalesOrderID = Forms!frmSales!SalesOrderID
RST!VinylRetail = Forms!frmSales!txtVinylTotal
RST!VinylGP£ = Forms!frmSales!txtVinylMarkup
RST!LabourRetail = Forms!frmSales!txtLabourTotal
RST!OtherRetail = Forms!frmSales!txtOtherTotal
RST!Postage = Forms!frmSales!txtPostage
RST!DiscountedSubTotal = Forms!frmSales!txtDiscountedTotal
RST!MarkupP = Forms!frmSales!Markup
RST!DiscountP = Forms!frmSales!txtDiscount
RST!Discount£ = Forms!frmSales!txtDiscTotal
RST!VAT£ = Forms!frmSales!txtVatAmount
RST!VATP = Forms!frmSales!VAT1
RST!TotalInCVAT = Forms!frmSales!TotalInCVAT
RST.Update
Set RST = Nothing
Set DB = Nothing
Me.Refresh
Else
End If
Cheers ...
Dave.