Solved Syntax error in query expression (1 Viewer)

[Momma]

I have a combobox in my form with a list of all my contacts as rowsource. I don't get any error when I select a name from the list. I also have an after update event and does exactly what it's supposed to do.
When I delete a name from the combobox and leave it empty I get this error:
Syntax error (missing operator) in query expression 'ContactID='.
highlighting this line in my code:
ContactTravel = Nz(DCount("ContactID", "tblTravel", "ContactID= " & CurrentOwnerID), 0)

My Combobox Rowsource is: SELECT qryContacts.ContactID, qryContacts.FullName FROM qryContacts ORDER BY qryContacts.FullName;

I've done a Debug Compile and no errors were found.

I was wondering if there is a workaround to allow a null value in the combobox.

Thank you in advance.

Private Sub CboBuyer_AfterUpdate()

Dim ContactTravel As Long
ContactTravel = Nz(DCount("ContactID", "tblTravel", "ContactID= " & CurrentOwnerID), 0)
Dim DogTravel As Long
DogTravel = DCount("DogID", "tblTravel", "DogID= " & Me.DogID)
Dim ContactInv As Long
ContactInv = Nz(DCount("ContactID", "tblInvoices", "ContactID= " & CurrentOwnerID), 0)
Dim DogInv As Long
DogInv = DCount("DogID", "tblInvoices", "DogID= " & Me.DogID)


If Me.CategoryID = 2 Then
     Beep
     Select Case MsgBox("This Contact is still on the Waiting list. Do you want to change it to Buyer?", vbYesNo)
        Case vbYes
        Me.CategoryID = 1
     End Select
End If

If Me.CategoryID = 1 And Me.CboBuyer > 0 Or Me.CategoryID = 2 And Me.CboBuyer > 0 Then
        
        If ContactTravel = 0 And DogTravel = 0 Then
            DoCmd.RunSQL "INSERT INTO tblTravel (ContactID, DogID) " & _
            "VALUES (CurrentOwnerID, DogID)"
        Else
        
            If ContactTravel = 1 And DogTravel = 0 Then
                DoCmd.RunSQL "INSERT INTO tblTravel (ContactID, DogID) " & _
                "VALUES (CurrentOwnerID, DogID)"
            End If
        End If
            
        If ContactInv = 0 And DogInv = 0 Then
            DoCmd.RunSQL "INSERT INTO tblInvoices (ContactID, DogID, InvoiceDate) " & _
                "VALUES (CurrentOwnerID, DogID, Date())"
        Else
        
            If ContactInv = 1 And DogInv = 0 Then
                DoCmd.RunSQL "INSERT INTO tblInvoices (ContactID, DogID, InvoiceDate) " & _
                "VALUES (CurrentOwnerID, DogID, Date())"
            End If
        End If
        
Forms!frmpuppybuyerlist.Form!frmPuppyBuyerlistsubf.Form!frmInvoice.Requery
Dim CurrentInvoiceID As Long
CurrentInvoiceID = Nz(Forms!frmpuppybuyerlist.Form!frmPuppyBuyerlistsubf.Form!frmInvoice.Form!InvoiceID, 0)
Dim SaleDescription As String
SaleDescription = "'Border Collie Puppy'"

        If DCount("ContactID", "tblInvoices", "ContactID= " & Me.CurrentOwnerID) > 0 Then
            DoCmd.RunSQL "UPDATE tblInvoices SET DogID = " & DogID & " WHERE ContactID=" & CurrentOwnerID
            DoCmd.RunSQL "INSERT INTO tblInvoiceDetails (ContactID, DogID, InvoiceID, Description, Amount) " & _
            " VALUES (" & CurrentOwnerID & ", " & DogID & ", " & CurrentInvoiceID & ", " & SaleDescription & ", " & Price & ")"
        End If
        
Me.cboStatus = 7

End If

Me.Recordset.Requery

End Sub

 

[cheekybuddha]

ContactTravel = Nz(DCount("ContactID", "tblTravel", "ContactID= " & CurrentOwnerID), 0)

You've got your Nz() in the wrong place. DCount() will never return Null, always a number, 0 or more.

Try:

ContactTravel = DCount("ContactID", "tblTravel", "ContactID= " & Nz(CurrentOwnerID, 0))

 

[Momma]

You've got your Nz() in the wrong place. DCount() will never return Null, always a number, 0 or more.

Try:

ContactTravel = DCount("ContactID", "tblTravel", "ContactID= " & Nz(CurrentOwnerID, 0))

Thankyou Cheekybuddha, that solved my problem!

 

[cheekybuddha]