I'm getting an error which I cannot explain. I'd be grateful if
someone else would look at it and help me figure out what is going on.

In a nutshell, I get the error "You tried to assign the Null value to
a variable that is not a Variant data type." when I change a value to
Null in query design view. The query is updatable and the Required
property for the changed column is set to "No", so changing the value
to Null should be allowed.

To see the problem for yourself, open the database referenced below,
then open the query ArgumentRW, delete ElementID 656, then hit Return.
You should get the error I mentioned. (If you don't, please let me
know.)

ftp://share.matsonconsulting.com/AssignNullError.mdb

Maybe I'm missing something obvious, but my best guess is that this is
a bug, and that under some circumstances, "updatable" queries don't
support the update operations they should support. If that guess is
correct, it would be very bad for me; this error is occurring in an
application which was designed to use updatable queries as the source
for all forms. If updatable queries aren't reliable, I'll have to do a
major redesign of the application to base my forms on tables instead.

I'm using Access 2003 SP3.


-TC

existential.philosophy (AT) gmail (DOT) com wrote:

I've never received that message that I can recall. I'm not quite sure why.
Although I generally use right joins in queries and not equal joins.

Yes, I did get the error message.

I don't have an explanation. However changing the queries Recordset Type to Dynaset
(Inconsistent Updates) or using a right join fixed this problem.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

existential.philosophy (AT) gmail (DOT) com wrote:

I don't know how many people attempt to "delete" the linked field data
in a query. Your current status is an inner join. If I also had 657 in
both tables then changed it to 656, it'd be ok because 656 exists.
Access doesn't seem to want assign a null to the link. I tried to
change it to 777 and it complained about that since there was no
matching id 777 record in Element.

This seems to work. Kinda dumb tho, breaking the link between the two
tables...but there must be a reason for you to do so.

SELECT Argument.ArgumentID, Argument.ElementID
FROM Argument LEFT JOIN Element ON Argument.ElementID = Element.ElementID;