Error

[krc547]

I have two tables. I have a fixed category table and a Route table. when I try to input data on a form it returns this error:

 

[pbaldy]

It sounds like you're trying to put a value into the category table that doesn't exist in the route table, and you have referential integrity set so as not to allow that.

 

[CJ_London]

Error message seems clear enough to me. Whichever is the primary table needs to have a parent record before you can have a child record

 

[krc547]

It sounds like you're trying to put a value into the category table that doesn't exist in the route table, and you have referential integrity set so as not to allow that.

So how do I turn off Referential integrity, or do i want to?

 

[mike60smart]

So how do I turn off Referential integrity, or do i want to?

No Do not turn off RI.
Just identify what is missing from the Parent Table

 

[pbaldy]

It's helpful if you want to make sure nobody can add a route to the category table that doesn't exist in the route table.

Guide to table relationships - Microsoft Support

Link tables in Access desktop databases by adding joins and creating relationships.

support.microsoft.com

 

[CJ_London]

would have thought that would be the other way round - category would be the primary table in this relationship since it is 'fixed' - and you would not be able to save a record in the route table until a category has been selected. Not enough info from the OP to determine.

For all we know, the form is based on a query combining both tables

 

[The_Doc_Man]

CJ's point is important. @krc547 - the rule is that Access HATES to have an orphaned record, which is defined as a child record that depends on a parent record - but the parent record doesn't exist. Referential Integrity is the Access feature that assures this fact. But the trick is to define the relationship so that the dependency "arrows" point in the right direction. Otherwise you have a child defining a parent, which is backwards.

First determine which record is INDEPENDENT (exists independent of anything else, even if it has no current child records). Then the dependent record will have a foreign key to match the primary key of the independent record. And the relationships will be made from the PK of the independent table to say "All records from (the independent table) and only matching keys from (the dependent table.)"

 

[krc547]

Would it be possible to upload a zip file of the database and maybe offer some feedback?

 

[mike60smart]

Would it be possible to upload a zip file of the database and maybe offer some feedback?

Yes an upload would help

 

[krc547]

Here is my database in zip form. Thank you

 

[GaP42]

You have copied data from the supporting reference tables to tblFixed Category. This would appear counter to good database design - ie database normalisation principles which are employed to overcome redundant data issues. You should only have one copy of a data item. Your forms and reports can draw this data in using the appropriate query. Modify tblFixed Category to remove the fields: Type, Route, Category and Revenue.
This is the source of your original issue I think. You are writing to the tblFixed Category via form adding "route" information, however the Route does not exist in the tblRoute (which has RI established between them via the RouteID). The form has as its record source the table Fixed Category.

The form then needs to be adjusted so Route is a combo - selecting from the list of items in the tblRoute - just as you have done for CategoryID. Remove the other Category combo - it is using a value list which is a duplicate of values in tblCategory and accessed/display via the CategoryID combo. Repeat this for Revenue.

See how you progress to this point before moving forward. Good luck.

 

[krc547]

You have copied data from the supporting reference tables to tblFixed Category. This would appear counter to good database design - ie database normalisation principles which are employed to overcome redundant data issues. You should only have one copy of a data item. Your forms and reports can draw this data in using the appropriate query. Modify tblFixed Category to remove the fields: Type, Route, Category and Revenue.
This is the source of your original issue I think. You are writing to the tblFixed Category via form adding "route" information, however the Route does not exist in the tblRoute (which has RI established between them via the RouteID). The form has as its record source the table Fixed Category.

The form then needs to be adjusted so Route is a combo - selecting from the list of items in the tblRoute - just as you have done for CategoryID. Remove the other Category combo - it is using a value list which is a duplicate of values in tblCategory and accessed/display via the CategoryID combo. Repeat this for Revenue.

See how you progress to this point before moving forward. Good luck.

Thank you