Error

krc547

Member
Local time
Today, 17:30
Joined
Sep 1, 2023
Messages
38
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:

1694629416418.png
 
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.
 
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
 
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?
 
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.

 
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
 
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.)"
 
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.

I also think the relationship might be backward but only you know that for sure.
 
Would it be possible to upload a zip file of the database and maybe offer some feedback?
 
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.
 
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
 
This is the cause of the original error message. You also have no validation for the form
1694725365952.png


Other problems on very quick examination
1. When you open a report or a form from a form, you MUST first save the current record or it will not appear on the report or form being opened.
Code:
Private Sub DataCheckbtn_Click()
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenReport "rDataCheck", acViewReport, "rDataCheck"
End Sub
2. Using a combo on the login form reduces the effectiveness of the login since you are giving the unauthorized user half of the information he needs to open the application.
3. You are creating a nightmare for yourself with the security you have defined. I get where you're going but that isn't the route. Take a look at my custom switchboard for an alternate idea. If you prefer the big button style, there is a sample in the second link. It isn't connected to security but it uses the same concept of the original Access switchboard with fixed items so you can copy the security from that sample in the secured database sample. The sample form uses 12 buttons. There are instructions in frmInstructions on how to expand the number of buttons. I always make the exit from the menu item #12. If you increase the number of buttons, use your max. You can modify the query so that it uses the users security level to build the switchboard so it will work the way you are envisioning your app to work but with a fraction of the work since the data is properly normalized.

If you have specific questions regarding the switchboard samples, start a new thread. I will respond if I see them. Be sure to @my name in the text so I will be notified the next time I log on. Others might be able to help also so don't direct your question to me, just tag me.
 

Users who are viewing this thread

Back
Top Bottom