Helpful Information
 
 
Category: Other Databases
Access Database - Help with Error message

My question for the problem I’m having is as follows: :confused:


I have a form that needs to display the values from several tables that are linked together link this:


Main table has a foreign key1 to look up the name in table 2 which uses key2 to look up the value in table 3 which uses key3 to lookup the value in table 4.


Several problems showed up when trying to add a new record.

When adding a record the user walks down the links in the opposite direction: using a combo box to select an item from table 4 which limits the selection on table 3 etc until, by selecting an item from table 2 you determine the value that’s placed in the FK of the main table. If I used bound combo boxes to do the downward walk I got error messages regarding the one to many relationship. I assume it was due to the middle tables having both relationships although I was using different keys. The unbound combo boxes had problems with obtaining the correct values for each record. They would have the correct values for the 1st loaded main table record but when it changed they didn’t change to reflect the new values. Also the user wants to be able to view records thru filters and the unbound didn’t work for filters.



So, I wound up using both: bound for viewing records and for filtering with unbound for adding new. (I don’t have to worry about mods)



So, my 1st question would be: is there a better way to get around this and the 2nd has to do with when I’m actually adding a record. Several of the error messages I’ve gotten include:



“The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship…..” .

This one occurs right after the form.before_update event.



My 2nd question is how do I get past this problem?



The main table has it’s own PK that’s auto-generated and unrelated to it’s FK. The PK is used as a FK in the main tables’ detail record. However, I assume that the complaint is from one of the other tables. Access of course doesn’t tell me which one is giving me fits, or if it’s all of them. I don’t need to update any of them, just the main table and add a corresponding record to the detail.



Any tips?

This sounds very familiar, the problem with linked tables normally appears to be related tot he type of table links, with strictly one-one relationships you will have few problems, otherwise the problem tends to be access doesn't know which bit ties where when you run a one-many relationship backwards.

The solution i've used is to use VBA code to create records, and code all the key fields manually, that way *you* are in control at all times.

I've had similar problems linking around eight tables in a query and getting 'this recordset is not updatable' errors, and when that was delt with random data corruption as the links didn't quiet resolve right everytime when adding data.

If you think this may help but need a few more pointers let me know and i'll see what i can cook up :-)










privacy (GDPR)