By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Weird Inter-Table Dependency!

P: n/a
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! :)
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
-----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/AwUBP6CI4IechKqOuFEgEQJDDgCguUVx5ySKGCj3H0n56rEOif mISUkAoP4d
3CG7a4oFeTdL5HeZWuJkMAs1
=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! :)

Nov 12 '05 #2

P: n/a
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" <me@privacy.com> wrote in message
news:sN****************@newsread4.news.pas.earthli nk.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/AwUBP6CI4IechKqOuFEgEQJDDgCguUVx5ySKGCj3H0n56rEOif mISUkAoP4d
3CG7a4oFeTdL5HeZWuJkMAs1
=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! :)


Nov 12 '05 #3

P: n/a
-----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/AwUBP6FfB4echKqOuFEgEQKs/ACfe5280/wd8TRTBGv/sNeUH1uXRw8AoIEX
wnMpqTs456paUODf4UGm7eVG
=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 >

Nov 12 '05 #4

P: n/a

"MX1" <mx*@mx1.abc> wrote in message
news:6I_nb.42014$mZ5.239534@attbi_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
Nov 12 '05 #5

P: n/a
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*****@nowhere.com> wrote in message
news:31********************@news-server.bigpond.net.au...

"MX1" <mx*@mx1.abc> wrote in message
news:6I_nb.42014$mZ5.239534@attbi_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

Nov 12 '05 #6

P: n/a
MX1
Removed default value of 0 in Master fixed the issue. Thanks for your time!
:)

"MGFoster" <me@privacy.com> wrote in message
news:0a*****************@newsread4.news.pas.earthl ink.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/AwUBP6FfB4echKqOuFEgEQKs/ACfe5280/wd8TRTBGv/sNeUH1uXRw8AoIEX
wnMpqTs456paUODf4UGm7eVG
=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 >

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.