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?

On Wed, 14 Apr 2010 20:26:01 -0700, Danny
<(E-Mail Removed)> wrote:

I don't quite follow you: the "many-side table" and the "child table"
are the same thing.

-Tom.
Microsoft Access MVP


>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?

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" <(E-Mail Removed)> wrote in message
news:1BA904E7-70E4-4F61-AE54-(E-Mail Removed)...
> 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?

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" <(E-Mail Removed)> wrote in message
> news:1BA904E7-70E4-4F61-AE54-(E-Mail Removed)...
> > 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?
>
>
> .
>

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" <(E-Mail Removed)> wrote in message
news:1FABE623-10E5-44D9-95DD-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
>> news:1BA904E7-70E4-4F61-AE54-(E-Mail Removed)...
>> > 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?
>>
>>
>> .
>>

On Thu, 15 Apr 2010 00:59:02 -0700, Danny <(E-Mail Removed)>
wrote:

>There is no way to allow or prohibit null values in an Access table.

Actually there is: set the field's Required property to Yes in table design
view.

--

John W. Vinson [MVP]

!!! 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" <(E-Mail Removed)> wrote in message
> news:1FABE623-10E5-44D9-95DD-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> >> news:1BA904E7-70E4-4F61-AE54-(E-Mail Removed)...
> >> > 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?
> >>
> >>
> >> .
> >>
>
>
> .
>

On Thu, 15 Apr 2010 10:38:06 -0700, Danny <(E-Mail Removed)>
wrote:

>!!! 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

Erm? What's the Master and Child Link Field? The Master should be the PK field
on the parent form, the Child should be the FK. I'd say that the FK field
should be required but that you should *not* use a zero default; that's more a
pain in the neck than any sort of benefit (since it will always be wrong and
need to be edited).

I suspect your master/child link isn't set up, or isn't working correctly.
--

John W. Vinson [MVP]

Danny,

You mentioned entering on a subform in your last post.

Assuming that the subform is actually being used as a subform, the PK value
of the parent record is automaticaly loaded into the FK of the child record
when the user creates a new record in the the subform.

This is also the common way that this issue gets taken care of.

Fred

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news(E-Mail Removed):

> I'd say that the FK field
> should be required but that you should *not* use a zero default;
> that's more a pain in the neck than any sort of benefit (since it
> will always be wrong and need to be edited).

....and it won't work unless you create a record in the parent table
with 0 as the PK value.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/