Weird Inter-Table Dependency!

[MX1]

OK, here's one that's driving me nuts. Three tables. They are...

Master Table
JobRef Reference Table
ToolRef Reference Table

I've got a form for Master that has a combo box to let you select from both
of these reference tables. When I make a new record, the system lets me
leave JobRef combo box null and save the record. However, I can't save the
record unless the ToolRef combo box has a value selected. This is driving
me nuts! I can't figure out what the difference is here. What's the
difference?

Neither is a required field in the table design and neither has any
validation rules, etc. Both are linked to Master the same way in
relationships. The only difference is that the linking ID field between Job
Ref and Master is of type TEXT and the linking ID between ToolRef and Master
is of type NUMBER. When I try to leave ToolRef drop down blank, I get the
following error:

You cannot add or change a record because a related record is required in
the table 'ToolRef'

This causes a lot of aggravation for users doing data entry. I want them to
leave this field blank if they want. Odd! Any help is appreciated as
always! :)

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Check if the field in the Master table has Allow Zero Length Strings
set to Yes. If so change it to No.

- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6CI4IechKq OuFEgEQJDDgCguU Vx5ySKGCj3H0n56 rEOifmISUkAoP4d
3CG7a4oFeTdL5He ZWuJkMAs1
=ipII
-----END PGP SIGNATURE-----
MX1 wrote:

OK, here's one that's driving me nuts. Three tables. They are...

Master Table
JobRef Reference Table
ToolRef Reference Table

I've got a form for Master that has a combo box to let you select from both
of these reference tables. When I make a new record, the system lets me
leave JobRef combo box null and save the record. However, I can't save the
record unless the ToolRef combo box has a value selected. This is driving
me nuts! I can't figure out what the difference is here. What's the
difference?

Neither is a required field in the table design and neither has any
validation rules, etc. Both are linked to Master the same way in
relationships. The only difference is that the linking ID field between Job
Ref and Master is of type TEXT and the linking ID between ToolRef and Master
is of type NUMBER. When I try to leave ToolRef drop down blank, I get the
following error:

You cannot add or change a record because a related record is required in
the table 'ToolRef'

This causes a lot of aggravation for users doing data entry. I want them to
leave this field blank if they want. Odd! Any help is appreciated as
always! :)

[MX1]

I checked this out. The problem I'm having is with the field and reference
table that has type set to Number - Long Integer. There is no option for
Allow Zero Length Strings. This is not a text field.
"MGFoster" <[e-mail] > wrote in message
news:sN******** ********@newsre ad4.news.pas.ea rthlink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Check if the field in the Master table has Allow Zero Length Strings
set to Yes. If so change it to No.

- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6CI4IechKq OuFEgEQJDDgCguU Vx5ySKGCj3H0n56 rEOifmISUkAoP4d
3CG7a4oFeTdL5He ZWuJkMAs1
=ipII
-----END PGP SIGNATURE-----
MX1 wrote:

OK, here's one that's driving me nuts. Three tables. They are...

Master Table
JobRef Reference Table
ToolRef Reference Table

I've got a form for Master that has a combo box to let you select from both of these reference tables. When I make a new record, the system lets me
leave JobRef combo box null and save the record. However, I can't save the record unless the ToolRef combo box has a value selected. This is driving me nuts! I can't figure out what the difference is here. What's the
difference?

Neither is a required field in the table design and neither has any
validation rules, etc. Both are linked to Master the same way in
relationships. The only difference is that the linking ID field between Job Ref and Master is of type TEXT and the linking ID between ToolRef and Master is of type NUMBER. When I try to leave ToolRef drop down blank, I get the following error:

You cannot add or change a record because a related record is required in the table 'ToolRef'

This causes a lot of aggravation for users doing data entry. I want them to leave this field blank if they want. Odd! Any help is appreciated as
always! :)

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, the error:

"You cannot add or change a record because a related record is
required in the table 'ToolRef'."

means that there is something in the ToolRef control that is NOT in
the table ToolRef. You may have accidently hit the space key when in
the control. Since you probably don't have a SPACE in the ToolRef
lookup table, the error occurs. Make sure you don't type anything
into the control and that there isn't any VBA code, or macro, that is
trying to put something in that control.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6FfB4echKq OuFEgEQKs/ACfe5280/wd8TRTBGv/sNeUH1uXRw8AoIE X
wnMpqTs456paUOD f4UGm7eVG
=1G1U
-----END PGP SIGNATURE-----

MX1 wrote:

I checked this out. The problem I'm having is with the field and reference
table that has type set to Number - Long Integer. There is no option for
Allow Zero Length Strings. This is not a text field.

< snip previous posts >

[David Hare-Scott]

"MX1" <mx*@mx1.abc> wrote in message
news:6I_nb.4201 4$mZ5.239534@at tbi_s54...

OK, here's one that's driving me nuts. Three tables. They are...

Master Table
JobRef Reference Table
ToolRef Reference Table

I've got a form for Master that has a combo box to let you select from both of these reference tables. When I make a new record, the system lets me leave JobRef combo box null and save the record. However, I can't save the record unless the ToolRef combo box has a value selected. This is driving me nuts! I can't figure out what the difference is here. What's the
difference?

Neither is a required field in the table design and neither has any
validation rules, etc. Both are linked to Master the same way in
relationships. The only difference is that the linking ID field between Job Ref and Master is of type TEXT and the linking ID between ToolRef and Master is of type NUMBER. When I try to leave ToolRef drop down blank, I get the following error:

You cannot add or change a record because a related record is required in the table 'ToolRef'

This causes a lot of aggravation for users doing data entry. I want them to leave this field blank if they want. Odd! Any help is appreciated as
always! :)

You have allowed one of the FKs to default to zero, provided referential
integrity is enforced, JET will allow it to be null but not zero unless
there is a corresponding PK in your reference table.

David

[MX1]

Bingo. The ID field in Master was defaulting to a value of 0. I got rid
of that and can now leave the field blank without the annoying error.
Thanks!

"David Hare-Scott" <pr*****@nowher e.com> wrote in message
news:31******** ************@ne ws-server.bigpond. net.au...

"MX1" <mx*@mx1.abc> wrote in message
news:6I_nb.4201 4$mZ5.239534@at tbi_s54...

OK, here's one that's driving me nuts. Three tables. They are...

Master Table
JobRef Reference Table
ToolRef Reference Table

I've got a form for Master that has a combo box to let you select from

both

of these reference tables. When I make a new record, the system lets

me

leave JobRef combo box null and save the record. However, I can't

save the

record unless the ToolRef combo box has a value selected. This is

driving

me nuts! I can't figure out what the difference is here. What's the
difference?

Neither is a required field in the table design and neither has any
validation rules, etc. Both are linked to Master the same way in
relationships. The only difference is that the linking ID field

between Job

Ref and Master is of type TEXT and the linking ID between ToolRef and

Master

is of type NUMBER. When I try to leave ToolRef drop down blank, I get

the

following error:

You cannot add or change a record because a related record is required

in

the table 'ToolRef'

This causes a lot of aggravation for users doing data entry. I want

them to

leave this field blank if they want. Odd! Any help is appreciated as
always! :)

You have allowed one of the FKs to default to zero, provided referential
integrity is enforced, JET will allow it to be null but not zero unless
there is a corresponding PK in your reference table.

David

[MX1]

Removed default value of 0 in Master fixed the issue. Thanks for your time!
:)

"MGFoster" <[e-mail] > wrote in message
news:0a******** *********@newsr ead4.news.pas.e arthlink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, the error:

"You cannot add or change a record because a related record is
required in the table 'ToolRef'."

means that there is something in the ToolRef control that is NOT in
the table ToolRef. You may have accidently hit the space key when in
the control. Since you probably don't have a SPACE in the ToolRef
lookup table, the error occurs. Make sure you don't type anything
into the control and that there isn't any VBA code, or macro, that is
trying to put something in that control.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6FfB4echKq OuFEgEQKs/ACfe5280/wd8TRTBGv/sNeUH1uXRw8AoIE X
wnMpqTs456paUOD f4UGm7eVG
=1G1U
-----END PGP SIGNATURE-----

MX1 wrote:

I checked this out. The problem I'm having is with the field and reference table that has type set to Number - Long Integer. There is no option for Allow Zero Length Strings. This is not a text field.

< snip previous posts >