!!! The default value of 0 is not working on subforms. I've set the default
value in both the table and the subform, but it doesn't seem to get set when
I attempt to enter data into the subform when the parent form record is
empty. Here is what happens:

- Main form record is empty
- Subform record is empty, and default value of 0 displays in the FK field
- I start typing data into the subform record, while main form record is
still empty
- FK field value changes from 0 to NULL

If I make the FK field Required, in addition to setting the default value to
0, then here's what happens:

- Main form record is empty
- Subform record is empty, and default value of 0 DOESN'T display in the FK
field
- I start typing data into the subform record, and immediately a prompt
tells me that I must enter a value in the FK field
- I click through the prompt, and finish entering data in the subform record
(main form record still empty)
- When I attempt to move off of the new subform record, I get a prompt
telling me that I can't change or add record because a related record is
required in the parent table

So this will do - it will prevent creation of orphaned records. But it seems
an awful lot of redundant field property values...



"Roger Carlson" wrote:

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?


.



.