View Full Version : Access Database - Help with Error message

Dec 31st, 2004, 03:23 AM
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?

claire rand
Jan 3rd, 2005, 04:23 PM
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 :-)