How do I handle this referential integrity situation?

[manning_news@hotmail.com]

Using 2003. I've got a simple 1:M relationship for employees and
classifications . A classification can be assigned to many employees/an
employee can only have one classification. I created a form for adding
employees/classifications . The classification can be left blank if not
known. I created a 2nd form for modifying employees/classification.
If the classification is left blank, or it's blank out, the 3201
referential integrity error occurs. I understand that there is not a
blank record in the classifications table so that's why the error. But
is there a way to leave the classification blank or blank it out? Do I
have to put a record in the classifications lookup table, like (no
classification) ? Thanks for any help or advice.

[pietlinden@hotmail.com]

Re: How do I handle this referential integrity situation?

if an employee can have no classification (and it's valid), then you
can't enforce referential integrity between employees and
classifications . If you set the RI constraint to none, then everything
will be fine.

[Anthony England]

Re: How do I handle this referential integrity situation?

<manning_news@h otmail.com> wrote in message
news:1136581777 .967716.123690@ f14g2000cwb.goo glegroups.com.. .[color=blue]
> Using 2003. I've got a simple 1:M relationship for employees and
> classifications . A classification can be assigned to many employees/an
> employee can only have one classification. I created a form for adding
> employees/classifications . The classification can be left blank if not
> known. I created a 2nd form for modifying employees/classification.
> If the classification is left blank, or it's blank out, the 3201
> referential integrity error occurs. I understand that there is not a
> blank record in the classifications table so that's why the error. But
> is there a way to leave the classification blank or blank it out? Do I
> have to put a record in the classifications lookup table, like (no
> classification) ? Thanks for any help or advice.[/color]


This is perfectly possible - just as you describe - without any need to
define a 'no classification' record in the classifications table. There is
something else you are doing wrong.
My guess is that you have something like
tblEmployee.Cla ssificationID which is a long integer with a default value of
zero. When you create the record, it tries to put zero in this field, and
of course you don't have a matching record where tblCalssificati on.ID=0.
So, certainly check any default values in your table design.

[Anthony England]

Re: How do I handle this referential integrity situation?

<pietlinden@hot mail.com> wrote in message
news:1136584418 .739401.229290@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> if an employee can have no classification (and it's valid), then you
> can't enforce referential integrity between employees and
> classifications . If you set the RI constraint to none, then everything
> will be fine.[/color]


Are you sure of what you are saying?

[David W. Fenton]

Re: How do I handle this referential integrity situation?

pietlinden@hotm ail.com wrote in
news:1136584418 .739401.229290@ z14g2000cwz.goo glegroups.com:
[color=blue]
> if an employee can have no classification (and it's valid), then
> you can't enforce referential integrity between employees and
> classifications . If you set the RI constraint to none, then
> everything will be fine.[/color]

Eh? All you need is for the foreign key to not be required. That
way, the FK can be Null, while still enforcing that any non-Null
value will be drawn from the PK of a related table.

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

[pietlinden@hotmail.com]

Re: How do I handle this referential integrity situation?

if an employee can have no classification (and it's valid), then you
can't enforce referential integrity between employees and
classifications . If you set the RI constraint to none, then everything
will be fine.

If you choose to enforce RI, then you have to classify each employee.
That's the way RI works...

[manning_news@hotmail.com]

Re: How do I handle this referential integrity situation?

There's no default value. For the user's benefit I store the
description, not an ID #.


Anthony England wrote:[color=blue]
> <manning_news@h otmail.com> wrote in message
> news:1136581777 .967716.123690@ f14g2000cwb.goo glegroups.com.. .[color=green]
> > Using 2003. I've got a simple 1:M relationship for employees and
> > classifications . A classification can be assigned to many employees/an
> > employee can only have one classification. I created a form for adding
> > employees/classifications . The classification can be left blank if not
> > known. I created a 2nd form for modifying employees/classification.
> > If the classification is left blank, or it's blank out, the 3201
> > referential integrity error occurs. I understand that there is not a
> > blank record in the classifications table so that's why the error. But
> > is there a way to leave the classification blank or blank it out? Do I
> > have to put a record in the classifications lookup table, like (no
> > classification) ? Thanks for any help or advice.[/color]
>
>
> This is perfectly possible - just as you describe - without any need to
> define a 'no classification' record in the classifications table. There is
> something else you are doing wrong.
> My guess is that you have something like
> tblEmployee.Cla ssificationID which is a long integer with a default value of
> zero. When you create the record, it tries to put zero in this field, and
> of course you don't have a matching record where tblCalssificati on.ID=0.
> So, certainly check any default values in your table design.[/color]

[manning_news@hotmail.com]

Re: How do I handle this referential integrity situation?

There has to be a way to do this. I haven't tried this but I could
trap the error. But once it's trapped can I tell the mdb to ignore it
and leave the field blank?

[Anthony England]

Re: How do I handle this referential integrity situation?


<manning_news@h otmail.com> wrote in message
news:1136587446 .471611.196630@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> There's no default value. For the user's benefit I store the
> description, not an ID #.
>
>
> Anthony England wrote:[color=green]
>> <manning_news@h otmail.com> wrote in message
>> news:1136581777 .967716.123690@ f14g2000cwb.goo glegroups.com.. .[color=darkred]
>> > Using 2003. I've got a simple 1:M relationship for employees and
>> > classifications . A classification can be assigned to many employees/an
>> > employee can only have one classification. I created a form for adding
>> > employees/classifications . The classification can be left blank if not
>> > known. I created a 2nd form for modifying employees/classification.
>> > If the classification is left blank, or it's blank out, the 3201
>> > referential integrity error occurs. I understand that there is not a
>> > blank record in the classifications table so that's why the error. But
>> > is there a way to leave the classification blank or blank it out? Do I
>> > have to put a record in the classifications lookup table, like (no
>> > classification) ? Thanks for any help or advice.[/color]
>>
>>
>> This is perfectly possible - just as you describe - without any need to
>> define a 'no classification' record in the classifications table. There
>> is
>> something else you are doing wrong.
>> My guess is that you have something like
>> tblEmployee.Cla ssificationID which is a long integer with a default value
>> of
>> zero. When you create the record, it tries to put zero in this field,
>> and
>> of course you don't have a matching record where tblCalssificati on.ID=0.
>> So, certainly check any default values in your table design.[/color][/color]


If that is not the problem, then you could try and narrow down the cause of
the error. Without using any forms, but just entering data in the tables
directly, can you create an employee with no classification? If not then we
need to look at the table structure in greater detail. If you can, then it
is the form design at fault.

[Anthony England]

Re: How do I handle this referential integrity situation?

<pietlinden@hot mail.com> wrote in message
news:1136584464 .607221.38670@g 14g2000cwa.goog legroups.com...[color=blue]
> if an employee can have no classification (and it's valid), then you
> can't enforce referential integrity between employees and
> classifications . If you set the RI constraint to none, then everything
> will be fine.
>
> If you choose to enforce RI, then you have to classify each employee.
> That's the way RI works...[/color]


If you create the following tables:

tblContact:
ConID Autonumber, PK
ConName Text, Length 50, Required, Do not allow zero length, Indexed No
Duplicates
ConType Long Integer, Not Required, Indexed Duplicates OK

tblType:
TypID Autonumber, PK
TypName Text, Length 50, Required, Do not allow zero length, Indexed No
Duplicates


.... you can enforce RI with a one to many relationship between tblType and
tblContact. This means that each contact can have a type - it doesn't have
to, but if it does have one, then it must be one in the table tblType.
That's the way RI works on my machine - but let me know if you cannot
reproduce this structure on yours.

[David W. Fenton]

Re: How do I handle this referential integrity situation?

manning_news@ho tmail.com wrote in
news:1136581777 .967716.123690@ f14g2000cwb.goo glegroups.com:
[color=blue]
> Using 2003. I've got a simple 1:M relationship for employees and
> classifications . A classification can be assigned to many
> employees/an employee can only have one classification. I created
> a form for adding employees/classifications . The classification
> can be left blank if not known. I created a 2nd form for
> modifying employees/classification. If the classification is left
> blank, or it's blank out, the 3201 referential integrity error
> occurs. I understand that there is not a blank record in the
> classifications table so that's why the error. But is there a way
> to leave the classification blank or blank it out? Do I have to
> put a record in the classifications lookup table, like (no
> classification) ?[/color]

Did you define the classification field as required?

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

[manning_news@hotmail.com]

Re: How do I handle this referential integrity situation?

No, it's not required. Here's the table setup:

tblEmployees:
ID, autonumber, PK
LastName, text, 50
FirstName, text, 50
Classification, text, 50, not required, not indexed, zero-length
allowed

tlkpClassificat ions:
Classification, text, 50, PK

[manning_news@hotmail.com]

Re: How do I handle this referential integrity situation?

Forgot to add that I can enter an employee record directly into the
table, then blank out the classification. So it must be something in
the form design like Anthony England suggested. FWIW, it's an unbound
form. I use DAO to write to the employee table.

[Anthony England]

Re: How do I handle this referential integrity situation?

<manning_news@h otmail.com> wrote in message
news:1136816557 .284367.285780@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> Forgot to add that I can enter an employee record directly into the
> table, then blank out the classification. So it must be something in
> the form design like Anthony England suggested. FWIW, it's an unbound
> form. I use DAO to write to the employee table.[/color]

Well that makes a huge difference. If it is unbound then presumably the
error occurs when you try to save the record and not directly after you edit
the control (you don't say whether this is a textbox or combo or what).
There are a number of ways you could be trying to save the record and you
haven't posted any code.
Two things come to mind:
1. My current guess is that instead of null as the classification, which
would be allowed, you are trying to save a zero-length string.
2. Before anyone else asks: " are you really sure unbound forms are
offering you advantages over bound ones?"

[manning_news@hotmail.com]

Re: How do I handle this referential integrity situation?

1. Thanks. That was the problem

2. Poor choice on my part. There was no reason for it to be unbound.
I changed it to bound.


Anthony England wrote:[color=blue]
> <manning_news@h otmail.com> wrote in message
> news:1136816557 .284367.285780@ g14g2000cwa.goo glegroups.com.. .[color=green]
> > Forgot to add that I can enter an employee record directly into the
> > table, then blank out the classification. So it must be something in
> > the form design like Anthony England suggested. FWIW, it's an unbound
> > form. I use DAO to write to the employee table.[/color]
>
> Well that makes a huge difference. If it is unbound then presumably the
> error occurs when you try to save the record and not directly after you edit
> the control (you don't say whether this is a textbox or combo or what).
> There are a number of ways you could be trying to save the record and you
> haven't posted any code.
> Two things come to mind:
> 1. My current guess is that instead of null as the classification, which
> would be allowed, you are trying to save a zero-length string.
> 2. Before anyone else asks: " are you really sure unbound forms are
> offering you advantages over bound ones?"[/color]