Related Records Error

  • Thread starter Thread starter Stacy
  • Start date Start date
S

Stacy

I have three tables that all relate based on "TypeCode"
I can add a record just fine, but I receive the following error when
trying to make a change to the third table:

"You can't add or change a record because a related record is required
in tblTypeCodeTable.

I am using Access 97.

1) tblValidTypeCodes - Lists all available Type Codes
Autonumber
TypeCode (Related to 2)
TypeCodeDescription
DDAorSAV

2) tblTypeCodeTable - Lists all Valid Bank / Type Code combinations
Autonumber
Type Code (Related to 1 and 3)
BankNumber
- You can't add a record to table two unless the information is in
table one.

3) tblGLTable - Lists all Valid GL Info relating to Valid Banks / Type
Codes
Autonumber
GLNumber
Type Code (Related to 2)
- You can't add a record to table three unless the information is in
table one and table two.

Any ideas on why I'm getting this error?
All suggestions are appreciated.
Thanks,
Stacy
 
I have three tables that all relate based on "TypeCode"
I can add a record just fine, but I receive the following error when
trying to make a change to the third table:

"You can't add or change a record because a related record is required
in tblTypeCodeTable.

I am using Access 97.

1) tblValidTypeCodes - Lists all available Type Codes
Autonumber
TypeCode (Related to 2)
TypeCodeDescription
DDAorSAV

2) tblTypeCodeTable - Lists all Valid Bank / Type Code combinations
Autonumber
Type Code (Related to 1 and 3)
BankNumber
- You can't add a record to table two unless the information is in
table one.

3) tblGLTable - Lists all Valid GL Info relating to Valid Banks / Type
Codes
Autonumber
GLNumber
Type Code (Related to 2)
- You can't add a record to table three unless the information is in
table one and table two.
Click to expand...

Stacy,

the error can also depend on how are you trying to change something.
But OTOH I'd recommend a change in your data structure. I think it
would be better to include in tblGLTable the Autonumber primary key
from tblValidTypeCodes instead of TypeCode.

Oh wait, there is something I don't quite understand (and maybe Access
neither). I see now, you related table 3 to 2 only using the TypeCode
regardless of the BankNumber. However, table 2 can have several
entries with the same TypeCode. So when joining tables 2 and 3 in a
query/form there can be ambiguities. Indeed, looking to the data
structure I couldn't say what exactly is the dependency between table
3 and the others.

If table 3 only needs a valid TypeCode regardless of bank numbers,
then you should only relate it to table 1(foreign key: either the
Autonumber or the TypeCode).

If table 3 needs both a valid TypeCode and a valid BankNumber, then
you should relate it to table 2 but not only with the TypeCode but
with the combination of TypeCode and BankNumber. Which leads us again
to the Autonumber as a foreign key (multi-field key with both fields
is also possible, but it makes things more difficult and error prone).

HTH

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Back
Top