Solved Invalid use of Null

[Momma]

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!

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

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

 

[ebs17]

=> Dim TotalPaid As Long Variant

In VBA, only the Variant data type can accommodate NULL content.

 

[Gasman]

Or explore the NZ() function?

 

[ebs17]

Where there cannot be a NULL, there is no need to treat it.

Elsewhere in the code:
Outstanding = TotalDue - TotalPaid

You cannot calculate with NULL, the undefined.

 

[cheekybuddha]

I would leave your declaration of TotalPaid As Long, and change the following:

' ...
60        Else
70            TotalDue = Nz([Forms]![frmPuppyBuyerList]![frmPuppyBuyerlistsubf].[Form]![frmInvoice].[Form]![frmInvoiceDetails].[Form]![txtTotal], 0)
80            TotalPaid = Nz([Forms]![frmPuppyBuyerList]![frmPuppyBuyerlistsubf].[Form]![frmInvoice].[Form]![frmPayments].[Form]![txtTotalPaid], 0)
90            Outstanding = TotalDue - TotalPaid
100           If Outstanding = 0 Then
110               CurrentDb.Execute "UPDATE tblInvoices SET Closed = 1 WHERE InvoiceID = " & CurrentInvoiceID & ";"
120           End If
130       End If
' ...

However, I also suggest that some of the above is not necessary.

Having a 'Closed' field in your table is probably unnecessary since you can calculate it whenever required (eg Closed = (Outstanding = 0) )

 

[Momma]

Thank you all for your suggestions. I certainly learned a few more things.
I've decided to go with Cheekybuddha's suggestion and it worked as expected.