So how do I turn off Referential integrity, or do i want to?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.
No Do not turn off RI.So how do I turn off Referential integrity, or do i want to?
You NEVER want to turn off RI. The problem is most likely caused by Access being too "helpful". In the current versions of Access, MS has elected to make the default for all numeric data types = 0. This works OK for some cases and helps newbees who have no concept of nulls. HOWEVER, it causes a big problem for foreign keys and I think that is the problem you are experiencing. MS was helpful when you created the FK field in the table and it automatically added 0 as the default value. Well, 0 is NEVER valid for an autonumber and so what you are seeing is the error message you get if you try to save a record where no FK was specifically selected so the value defaulted to 0. Change the default to null (just delete the 0 to make the value "empty"). Then, you will be able to save the record without a FK. But, if you want to require the FK as you would in many cases, then you need to set the required property to Yes. In that case, Access will not allow you to save a record unless you enter a valid non-zero, non-null, value for the FK.So how do I turn off Referential integrity, or do i want to?
Yes an upload would helpWould it be possible to upload a zip file of the database and maybe offer some feedback?
Thank youYou 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.
Private Sub DataCheckbtn_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "rDataCheck", acViewReport, "rDataCheck"
End Sub