New Record with Auto-Number Primary Key Joined to Multiple Tables

[MeilingM]

Hi, all,

Using Access 2010, and this is my first db ever!

Thought I had the "simple stuff" down...

See attached pics for table relationships and query design.

• Primary Key in InsClaims Table is an Auto-Number.
• I can easily add an entry to the InsClaims Table. Start typing in the Claim Number, and the Auto-Number generates a new ID.
• Even though the "New Record" row is there, I can't add an entry through the MedClaims Query. Try to type in the Claim Number field, and get this error: "Cannot add record(s); join key of table 'InsClaims' not in recordset."

I assume this has something to do with how/when Auto-Numbers are generated?? Certainly I'm missing something simple again here??

Thanks.

 

[Alansidman]

Why are you trying to enter data through a query? Why not use a form? This is what forms are for.

 

[MeilingM]

I first encountered the problem trying to enter a new record through the form. Made sure "Data Entry = Yes", but still no-go.

Back-tracked to the query behind the form, and had exact same behavior trying to enter a new record in the query.

I figured my problem was in the query, not the form, so I posted in the "Queries" forum. <shrug>

 

[Alansidman]

Here's a tutorial on using an unbound form to enter data into a table using a query. Compare what Mike offers here to how you are doing your data entry. See if you can uncover the issue as without actually seeing your db, it will be difficult to determine what is causing the issue.

http://www.datapigtechnologies.com/flashfiles/addformrecordsnocode.html

 

[MeilingM]

Started the video, but the text boxes in my form are "bound".

I took the MedClaims query (design in pics attached to OP), went to the Create tab, and clicked Form. On that form, I clicked the new record button at the bottom. I tried to type in a Claim Number and got the above error.

I back-tracked to the query to see if it was read-only. It wasn't, but trying type into the new record row, I had the same error again. (See previous post.)


Since it's a database of my family's medical information, besides being a new member, didn't want to post the actual db.

I'm thinking this has something to do with the ID field (Primary Key) being an auto-number field and the ID field (Primary Key) being linked to so many other tables. (relationships in pics attached to OP)

Maybe there's some practice or quirk of using an auto-number field that I'm unaware of??

 

[MeilingM]

Anyone new on this morning who might be able to help me??

 

[MeilingM]

Anyone? Anyone?? Bueller... Bueller...

Got swamped at work for a week, but still no other responses??

Still need help!!

 

[bob fitz]

Perhaps you need to add the ID field form the Providers table to the grid.

 

[MeilingM]

Having ruled out all the reasons on Allen Browne's Read-Only Query page, I finally thought to search on the actual error message I was getting and found this page: rogersaccessblog.blogspot.com/2009/11/this-recordset-is-not-updateable-why.html


I started working through the solutions to the applicable example situations:

• Enforcing referential integrity
• Enabling Cascading Updates
• Adding the Foreign Key on the One side of the 1:M relationship

It seems I failed at this last step!! I had it backwards and upside-down! I was adding other tables (and their matching field) that the InsClaims are linked to!! It was the Foreign Key for Providers that I was missing...


THANK YOU SO MUCH, Bob!! Wish I could "thank" your post twice!!

 

[bob fitz]

You're welcome. Glad you've got it sorted.