Handling of Nulls in If statement

[lcook1974]

Good afternoon,

I have this little bit of code that when the person hits the button it will check to see if they already exist and if so then run and "update" query otherwise it will run an "insert into" (not written yet) query.

I get an error message that says invalid use of Nulls...

my question is how do I handle that for that bit of code? is there a better way to do this instead of the way I am currently?

Private Sub cmdSaveRecord_Click()
Dim db As Database
Dim rst As Recordset
'Dim strUpdateSQL As String
Dim intEmpID As Integer
Dim blnCheckEmployee As Boolean
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From CopyOfContact")
 
'Checks to see if the contact is already in database
If (DCount("First", "CopyOfContact", "'Me.txtFirstName'") And _
    DCount("Last", "CopyOfContact", "'Me.txtLastName'")) > 1 Then
    
    'returns a true value
    blnCheckEmployee = True
    
    'Where I get the invalid use of null if it is a new contact
   intEmpID = DLookup("ContactID", "CopyOfContact", _
                    "[Last] = '" & Me.txtLast & " '  And [First] = '" & Me.txtFirst & " ' ") = Me.txtEmpID
    
End If
'If contact exists then find the unique indentifier
If blnCheckEmployee = False Then
    'Here is where I would put the "insert into" query
        MsgBox "New Contact"
   
   ElseIf _
     IsNull(intEmpID = DLookup("ContactID", "CopyOfContact", _
                      "[Last] = '" & Me.txtLast & " '  And [First] = '" & Me.txtFirst & " ' ")) Then
                   
    End If
                       

    
   If MsgBox("This person already exists " & intEmpID & "...would you like to update this contact?", 307, "CONTACT EXISTS") = vbYes Then
 
'this part works fine...
    ' DoCmd.OpenQuery "updateContacts"
     MsgBox " You updated " & intEmpID & " " & Me.txtFirst & " " & Me.txtLast
          
     
     End If
 
End Sub

Larry

 

[vbaInet]

Have a look at the Nz() function.

 

[lcook1974]

Didn't think about that...Thank you!

I did try it...Now it doesn't show the error but goes straight passed everything to the Update query message. The message box appears and says "("This person already exists 0...would you like to update this contact?" When there isn't a 0 Id number...

any ideas? I think I'll start to work on another solution

Larry

 

[vbaInet]

If you want to break out of your code at any point, use Exit Sub.

 

[lcook1974]

okay...I'll re-run / re-write some things and get back with you if I have any issues..

Thanks VBAInet!!!!

 

[Khalid_Afridi]

Why you use the Dcount() function here? you can use WHERE clause instead.
Set rst = db.OpenRecordset("Select * From CopyOfContact WHERE....."

 

[vbaInet]

okay...I'll re-run / re-write some things and get back with you if I have any issues..

Thanks VBAInet!!!!

You're welcome. See how you get on.