You can give your foreign key a Default Value (ie. 0 (zero) for a numeric)
so that it will not be Null when a new record is created.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Danny" wrote in message
...
I did not know this - that null foreign key values are allowed. Wow. I've
worked with relational databases for over ten years, too. Hmm...

BUT: There is no way to allow or prohibit null values in an Access table.
For a text field, zero length strings can be allowed or prohibited, but
not
null values. Another way to deal with this is to set Required to Yes for
these fields. But I have to do something, because I don't want users being
able to inadvertently enter data into a subform where the main form has no
data.

Thanks for the education in RI and null values

"Sylvain Lafontaine" wrote:

Referential integrity is checked only for Non-Null values; so this is the
expected behavior here. If you don't want to have any Null value in your
foreign key, set it up as Not-Null in order to forbid null value in the
first place.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Danny" wrote in message
...
Howdy.

Using Acc 2007. Have two tables related one-to-many. Ref. integrity
being
enforced, without any cascading updates or deletes. Data in both
tables.

Problem: I can add new records to the many-side table, without any
value
for
the foreign key, and the new record is created without error or prompt
from
Access. I can do this both in the child table directly, and using a
form/subform.

On the other hand, if I create a new child record and enter a FK value
that
doesn't exist in the parent table, I get a message saying that a
related
record in the parent table is required, and Access prohibits creation
of
orphan.

What the heck is going on?


.