VBA Update not working for null values

[jslings]

I have VBA behind a membership management form that allows a user to assign a contactID to multiple parcel records in a table at once. When the user updates the contactID for one of the parcel record using the form, a pop up asks if the update should be applied to all remaining parcel records. If the user clicks yes, then the contactID is updated for all parcel records. When the update involves adding or changing the contactID to a number, the code works fine. When the update is to remove a contactID (i.e. clear it out to set as null), then a syntax error pops up. I'm not sure how to edit the VBA to update the contactIDs for all parcels to null in these cases.

current vba:



CurrentDb.Execute "UPDATE tbl_Parcels SET tbl_Parcels.ContactID = " & Me.ContactID & " WHERE MemberID = " & Me.MemberID


The issue occurs when me.ContactID = null

The table does allow nulls in the field. We tried changing the = to "is" and that did not solve the problem.

Thanks for your help!

[pbaldy]

Try

CurrentDb.Execute "UPDATE tbl_Parcels SET tbl_Parcels.ContactID = " & Nz(Me.ContactID, "Null") & " WHERE MemberID = " & Me.MemberID

[isladogs]

Well i was going to suggest:

 If Nz(Me.ContactID, 0) <> 0 Then        
         CurrentDb.Execute "UPDATE Members SET Members.ContactID = " & Me.ContactID & " WHERE MemberID = " & Me.MemberID
    Else
        CurrentDb.Execute "UPDATE Members SET Members.ContactID = Null WHERE MemberID = " & Me.MemberID
    End If

but Paul's solution which I've just tested works and is simpler

[jslings]

Nz(Me.ContactID, "Null") did the trick! Simple and effective. Thanks for your help!

[pbaldy]

Happy to help and welcome to the site by the way!