Hi everyone!
I have a form which I use to allocate the puppies we have to a certain buyer. At the bottom I show the invoice, which is created in the after update of the CurrentOwner(cboBuyer) combobox. The InvoiceID is linked to the OwnerID and also have a field for the DogID which is stored in tblInvoices. I have a separate table to record payments, which is tblPayments. The PaymentID is linked to the InvoiceID.
I have a field in the tblInvoice called Close (Y/N) and code in the Exit Event of the CurrentOwner(cboBuyer) combobox. The code mark the Invoice as closed if the Outstanding(Variable) is 0.
My problem is that I get an Invalid use of Null if no payments has been made. I'm not sure how to define the TotalPaid variable if there is a Null value.
Any help is highly appreciated
Thank you!
I have a form which I use to allocate the puppies we have to a certain buyer. At the bottom I show the invoice, which is created in the after update of the CurrentOwner(cboBuyer) combobox. The InvoiceID is linked to the OwnerID and also have a field for the DogID which is stored in tblInvoices. I have a separate table to record payments, which is tblPayments. The PaymentID is linked to the InvoiceID.
I have a field in the tblInvoice called Close (Y/N) and code in the Exit Event of the CurrentOwner(cboBuyer) combobox. The code mark the Invoice as closed if the Outstanding(Variable) is 0.
My problem is that I get an Invalid use of Null if no payments has been made. I'm not sure how to define the TotalPaid variable if there is a Null value.
Any help is highly appreciated
Thank you!
Code:
Private Sub CboBuyer_Exit(Cancel As Integer)
Dim TotalDue As Long
Dim TotalPaid As Long
Dim Outstanding As Long
Dim CurrentInvoiceID As Long
Dim CurrentDogID As Long
Dim CurrentContactid As Long
10 CurrentDogID = Me.DogID
20 CurrentContactid = Nz(Me.CurrentOwnerID, 0)
30 CurrentInvoiceID = Nz(DLookup("InvoiceID", "tblInvoices", "ContactID=" & CurrentContactid & " and dogid=" & CurrentDogID), 0)
40 If CurrentContactid = 0 Then
50 Exit Sub
60 Else
70 TotalDue = [Forms]![frmPuppyBuyerList]![frmPuppyBuyerlistsubf].[Form]![frmInvoice].[Form]![frmInvoiceDetails].[Form]![txtTotal]
80 TotalPaid = [Forms]![frmPuppyBuyerList]![frmPuppyBuyerlistsubf].[Form]![frmInvoice].[Form]![frmPayments].[Form]![txtTotalPaid]
90 Outstanding = TotalDue - TotalPaid
100 If Nz([TotalDue], 0) - Nz([TotalPaid], 0) = 0 Then
110 CurrentDb.Execute "UPDATE tblInvoices SET Closed = 1 WHERE InvoiceID = " & CurrentInvoiceID & ";"
120 End If
130 End If
140 Forms!frmPuppyBuyerList.Form!frmPuppyBuyerlistsubf.Form!frmInvoice.Requery
End Sub
Code:
Private Sub CboBuyer_Exit(Cancel As Integer)
Dim TotalDue As Long
Dim TotalPaid As Long
Dim Outstanding As Long
Dim CurrentInvoiceID As Long
Dim CurrentDogID As Long
Dim CurrentContactid As Long
10 CurrentDogID = Me.DogID
20 CurrentContactid = Nz(Me.CurrentOwnerID, 0)
30 CurrentInvoiceID = Nz(DLookup("InvoiceID", "tblInvoices", "ContactID=" & CurrentContactid & " and dogid=" & CurrentDogID), 0)
40 If CurrentContactid = 0 Then
50 Exit Sub
60 Else
70 TotalDue = [Forms]![frmPuppyBuyerList]![frmPuppyBuyerlistsubf].[Form]![frmInvoice].[Form]![frmInvoiceDetails].[Form]![txtTotal]
80 TotalPaid = [Forms]![frmPuppyBuyerList]![frmPuppyBuyerlistsubf].[Form]![frmInvoice].[Form]![frmPayments].[Form]![txtTotalPaid]
90 Outstanding = TotalDue - TotalPaid
100 If Nz([TotalDue], 0) - Nz([TotalPaid], 0) = 0 Then
110 CurrentDb.Execute "UPDATE tblInvoices SET Closed = 1 WHERE InvoiceID = " & CurrentInvoiceID & ";"
120 End If
130 End If
140 Forms!frmPuppyBuyerList.Form!frmPuppyBuyerlistsubf.Form!frmInvoice.Requery
End Sub