Share via

The Microsoft Access database engine cannot find a record in the table [TableName] with key matching field(s) [PrimaryKeyFieldName]

Anonymous
2010-11-16T04:07:54+00:00

I have normalized a single table database into 3NF (i.e. multiple tables), but notice when I try to add a new record to the main table, I get the above error message.  My question is: how do I get the Primary Key of the new record in the main table to populate tables related by that Primary Key? (i.e. how do I enter the Primary Key ONCE and have it appear in each related table?)

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

ScottGem 68,810 Reputation points Volunteer Moderator
2010-11-16T13:42:58+00:00

Hey Tom,

Sorry, not a longtime Access user.  My database doesn't have any subforms.  It has one main table "Member" with an AutoNumber Primary Key, to which 4 other tables are related.  The form I'm using queries the tables for all fields needed for data entry (I've changed "Data Entry" in the "Property Sheet" to Yes). Is there a way to set this up so that ANY insert/update to the main table Primary Key is propagated to all related tables as Foreign Keys?

It sounds like you are trying to enter data into 5 tables using one main form. That's not going to work. You NEED to be using subforms. Create your main form bound to the Members table and then subforms to each of the child tables. Use the Form wizard to create your forms.

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.spaces.live.com/blog Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Was this answer helpful?

8 people found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2010-11-16T04:44:50+00:00

    You have a database without forms? Curious.

    The short answer is No. When you run an Append query, it MUST be on a single table. When you run an Update query, you can sometimes update both fields in the parent table and the child table. But there is no automatic-filling-in-of-FK-values other than in the scenario I outlined in first instance.

    It *is* possible to cascade-update an existing PK value to its existing child tables' FK values. Go to the Relationships window, create or select a relationship, right-click Properties, and select Cascade Update. This only works if the primary key in the parent table is updatable (so, not an Autonumber).

    -Tom. Microsoft Access MVP

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-11-16T04:38:30+00:00

    Hey Tom,

    Sorry, not a longtime Access user.  My database doesn't have any subforms.  It has one main table "Member" with an AutoNumber Primary Key, to which 4 other tables are related.  The form I'm using queries the tables for all fields needed for data entry (I've changed "Data Entry" in the "Property Sheet" to Yes). Is there a way to set this up so that ANY insert/update to the main table Primary Key is propagated to all related tables as Foreign Keys?

    Was this answer helpful?

    0 comments
  3. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2010-11-16T04:16:29+00:00

    Access form/subform does that for you. For example you have Orders form with OrderDetails subform. You set the LinkMasterFields and LinkChildFields properties of the subform control to OrderID, and you're done. Access will automatically insert the foreign key value in the child table.

    -Tom. Microsoft Access MVP

    Was this answer helpful?

    0 comments