How to change a link field to null in a query under form

[herbivorous]

My form links tblPeople and tblAddresses via AddressID, the primary key of tblAddresses. I am trying to run an event to set People.AddressID to null, thus emptying a couple of address fields. Just having the module say

People.AddressID = Null

gives me an error complaining that the variable is not a variant, suggesting that Null is simply not the right word, though I suspect that since it is the link field, the complaint is actually a deeper one, like wait, no dude, that's too screwed up, I can't let you change that.

A more involved module that changes the underlying table outside of the form gives me a dialog that says another user has changed the record and am I cool with that, which is not something I want to be confusing my users with.

It seems like there should be some simple way to effect this change and update the form, but it eludes me just now. I could remove tblAddresses from my query and display the address info via DLookups, but that hardly seems efficient.

Scott, who knows just enough to be dangerous.

 

[vbajock]

Try

People.AddressID = ""

 

[herbivorous]

Same error, unfortunately.

 

[vbajock]

Sorry, should have read your post closer. You can't assign an empty value to a primary key.

 

[herbivorous]

It isn't a primary key, though it is linked to the primary key in the underlying query. It is simply a long integer.

The query links the table, and I need a way to break that association when someone moves to another organization -- the user will from here be able to either select from one of several possible addresses for the new organization, or will be able to enter entirely new address information.

Thanks for your efforts.