Prev: Vehicle Waiver Database
Next: Auto Populated a Table From SQL View When New Record Shows in View
From: John W. Vinson on 15 Apr 2010 12:38
On Thu, 15 Apr 2010 00:59:02 -0700, Danny <Danny(a)discussions.microsoft.com>
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]
From: Danny on 15 Apr 2010 13:38
!!! 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/wa.exe?SUBED1=ACCESS-L
>
>
> "Danny" <Danny(a)discussions.microsoft.com> wrote in message
> news:1FABE623-10E5-44D9-95DD-E83543A0946C(a)microsoft.com...
> >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" <Danny(a)discussions.microsoft.com> wrote in message
> >> news:1BA904E7-70E4-4F61-AE54-247CB5268418(a)microsoft.com...
> >> > 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?
> >>
> >>
> >> .
> >>
>
>
> .
>
From: John W. Vinson on 15 Apr 2010 18:34
On Thu, 15 Apr 2010 10:38:06 -0700, Danny <Danny(a)discussions.microsoft.com>
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]
From: Fred on 16 Apr 2010 08:14
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

From: BruceM via AccessMonster.com on 16 Apr 2010 10:33
Assuming the Link Child and Link Master properties of the subform control are
properly set, as John pointed out.


Fred wrote:
>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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201004/1

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Vehicle Waiver Database
Next: Auto Populated a Table From SQL View When New Record Shows in View