×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Can't add records because of Join Key

Can't add records because of Join Key

Can't add records because of Join Key

(OP)
I created a query for a form using the query wizard.  It uses three tables, and there isn't any criteria.  When I look at the query, I can see all the records.

Then I used the query to create a form, also using the wizard.  When I open it, it goes right to the first record.  But when I try to add in a record for the fields from one specific table, tells me "Cannot add records: Join Key of tblMyTable1 not in recordset."  I can input on fields from tblMyTable2 and tblMyTable3 without any problems.

I tried deleting the form and query and starting again--with the same result.  I have gone in checked out the other forms, and there isn't any problems.  Any thoughts?

Linda Adams
Garridon@aol.com
Linda Adams Online
I'm a professional writer, published internationally.

Replies continue below

Recommended for you

RE: Can't add records because of Join Key

Whatever field in tblMyTable1 is the join field to the other tables must be available to the form, so add it to the query on which the form is based.

That should do it.

Kathryn


RE: Can't add records because of Join Key

Typically, in a normalized database you wouldn't be updating data in three tables using a join. Most times a child table will be linked as a subform to the parent table which is the source for the main form. If there are 2 child tables then 2 sub forms. This is the way Access is designed to work and is a safe programming practice.

RE: Can't add records because of Join Key

(OP)
Only one actual table is being updated from this form, the one I'm having join problems with.  The other two tables are used for information that is repeated over and over, like the status (inactive/active) and the state.

Linda Adams
Garridon@aol.com
Linda Adams Online
I'm a professional writer, published internationally.

RE: Can't add records because of Join Key

When you use a join, Access isn't going to know that YOU only want to update one table. It is best to put the repeating information on a main form and the records you want to update on a sub form. Do a query for the main form with the repeating data and then LINK the sub form through the foreign key (a key from the repeating data) to the main form. If your database is not set up to do this then you may want to review the design, if possible, since a properly normalized database will save you problems in the future. The processing in Access is designed to work well with normalized data.  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close