Cannot add record(s); join key of table ERROR PLEASE Save me!

Hi all,
I am doing an accounting assignment on access which requires a form with subform for an order which can have many products. The products are in the subform and in the main form I have the order #, customer details date salesperson etc etc. After alot of trouble trying to do the subform and getting it to total, I have now run into the problem of Cannot add record(s); join key of table ' orders' not in recordset. When I try to enter new data into my form!!

I have tried reading up on this with the relationship thing but I just don't get it and its really driving me NUTS
I'm no expert on access and I'm really frustrated now, I have tried looking at the northwind template which absolutely BLEW me away! it is so amazing ! I could never do that! I just want to be able to enter data into my form!

I have attached a word document which shows the relationships and the form which I am using. If anyone has any suggestions PLEASE advise!!
Attached Files Access Database Problem.doc (465.5 KB, 9 views) Reply With Quote 04-20-2011, 06:56 AM #2 jzwp11 VIP Windows 7 64bit Access 2010 64bit Join Date Jun 2010 Location Dayton, OH Posts 2,892 Welcome to the forum!

The main form should be based on only the order table. Within the main form, you would use combo boxes to supply the employee, customer and shipper. The subform should be based only on the order detail table and you would use a combo box to populate the products ordered. I am assuming that you used queries that joined multiple tables and based your forms on the queries.

Additionally, you would not have the subtotal field in the order detail table as calculated values are generally not stored (you calculate it on the fly when you need the subtotal in forms, reports or queries).


Post your answer or comment

comments powered by Disqus
Hello,

I am making some good progress with the help I've gotten here over several months of trying to put this project together.

I now have a form based on a query which draws from 4 tables.
I'm told that if I try this that I may not be able to update my tables.
Sure enough, there is one table which I cannot update.

But, what I just realized is that when the form wizard used a query to base your form on, it basically still only uses the table themselves and knows how to manipulate the links and so on, when creating the form/suborm. If I'm wrong about that concept, please let me know.

OK the Problem:

A table I call ServiceNotes depends on a unique Id in a table that depends on another unique id in another table, and so on, and so on.
That's 4 tables all together.

I get the following error not right when I click the new record button, when I actually begin to type in data, which is also on the same line as "autonumber":

Cannot add record(s); join key of table 'ServiceNotes' not in Recordset

AND, here is the Record Source for the second subform on the form,
which draws the unique Id from the first of 2 subforms:

SELECT [Service].[ServiceTicket], [ServiceNotes].[TypeofService], [ServiceNotes].[Qty], [ServiceNotes].[Description], [ServiceNotes].[Price], [ServiceNotes].[Ext], [ServiceNotes].[Labor], [ServiceNotes].[Notes], [Service].[UniqueCarID] FROM ([Service] INNER JOIN [ServiceNotes] ON [Service].[ServiceTicket] =[ServiceNotes].[ServiceTicket])


Can you help me make that second subform updateable? Which would ultimately update the 4th table?

Thanks,
Jim

Alright. So after starting a new database from scratch, I have been able to successfully auto-fill a row in a form.

The dilema, though, is that I cannot add another row in the form without manually going to tblPartsPerOrder and starting a new row by inputting the JCSnumber. How do I make it so that this is automatic?

The error message is: "Cannot add record(s); Join key of table 'tblPartsPerOrder' not in recordset.

I have attached a copy of the database I am testing this on. Attached Files Copy of BOMtesting.zip (59.4 KB, 7 views) Reply With Quote 09-14-2011, 11:31 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,121 Unfortunately, Windows compression is not recognizing that zip file and won't extract.

I'mmm baaaaacccccckkkkk!

Got another one here that is driving me buggy. I've searched the forum and I'm just not finding an answer but that could because I'm not framing my search parameters right. I digress.

I have two databases. The main db holds time and billing information for jobs. The second (new) db is for the status reports I have to produce. Once a job is completed I need to record certain information specific to that job in a status report for the trustees. I'm trying to steamline the current process but I've hit a brick wall or actually in this case a run-time error wall.

On the Job form in the main db there is a button to Add to Status Reports. Clicking on this copies some of the job specific data and is supposed to paste that information into the _tbl_Status_qry form in the new db. It appears the copy procedure works fine, the second db opens but I get this run-time error:

Run-time error '3348': Cannot add record(s); join key of table 'tbl_Status' "not in recordset." Clicking the Help button provides absolutely no help whatsoever and I can't find anything specific to this error in the forum.

Here is the code for the click event on the Add to Status button:

Private Sub cmdStatusRpt_Click()

'Me.AllowEdits = True

Me.JobID.Enabled = True 'enables specific controls to allow copy
Me.FundID.Enabled = True
Me.EmpIDCombo.Enabled = True
Me.YrSelect.Enabled = True
Me.TypeCombo.Enabled = True
Me.AudID_Combo.Enabled = True
Me.LtrDate.Enabled = True
Me.FldDate.Enabled = True
Me.RptDate.Enabled = True
Me.ExamPer.Enabled = True
Me.Location.Enabled = True

DoCmd.GoToControl "JobID" 'copies job specific info for status report
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "FundID"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "EmpIDCombo"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "YrSelect"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "TypeCombo"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "AudID_Combo"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "LtrDate"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "FldDate"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "RptDate"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "ExamPer"
DoCmd.RunCommand acCmdCopy
DoCmd.GoToControl "Location"
DoCmd.RunCommand acCmdCopy
Me.JobID.Enabled = True 'disables specific controls to after copy
Me.FundID.Enabled = True
Me.EmpIDCombo.Enabled = True
Me.YrSelect.Enabled = True
Me.TypeCombo.Enabled = True
Me.AudID_Combo.Enabled = True
Me.LtrDate.Enabled = True
Me.FldDate.Enabled = True
Me.RptDate.Enabled = True
Me.ExamPer.Enabled = True
Me.Location.Enabled = True

Dim accapp As Access.Application 'opens up WP Status db
Set accapp = New Access.Application
accapp.OpenCurrentDatabase ("c:UsersCharlieDocumentsAccess FilesWPAS PR ProgramDatabaseWP_Status.accdb")
accapp.Visible = True
accapp.DoCmd.RunCommand acCmdAppMaximize
accapp.UserControl = True
Set accapp = Nothing
End Sub

Here is the code in the Status db Form_Load event:

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
Me.FundID.Enabled = True 'enables specific controls before pasting info
Me.JobID.Enabled = True
Me.EmpIDCombo.Enabled = True
Me.YrSelect.Enabled = True
Me.TypeCombo.Enabled = True
Me.AudID_Combo.Enabled = True
Me.LtrDate.Enabled = True
Me.FldDate.Enabled = True
Me.RptDate.Enabled = True
Me.ExamPer.Enabled = True
Me.Location.Enabled = True

DoCmd.GoToControl "FundID" 'paste info from Jobs form
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "JobID"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "EmpIDCombo"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "YrSelect"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "TypeCombo"
oCmd.RunCommand acCmdPaste
DoCmd.GoToControl "AudID_Combo"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "LtrDate"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "FldDate"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "RptDate"
oCmd.RunCommand acCmdPaste
DoCmd.GoToControl "ExamPer"
DoCmd.RunCommand acCmdPaste
DoCmd.GoToControl "Location"
DoCmd.RunCommand acCmdPaste

Me.FundID.Enabled = False 'disables specific controls after pasting info
Me.JobID.Enabled = False
Me.EmpIDCombo.Enabled = False
Me.YrSelect.Enabled = False
Me.TypeCombo.Enabled = False
Me.AudID_Combo.Enabled = False
Me.LtrDate.Enabled = False
Me.FldDate.Enabled = False
Me.RptDate.Enabled = False
Me.ExamPer.Enabled = False
Me.Location.Enabled = False
End Sub

The form uses _tbl_Status_qry as the record source, here is the SQL statement:

SELECT tbl_Status.FundID, tbl_Status.JobID, tbl_Status.YrSelect, Employers.Employer, tbl_Status.EmpID, JobType.Type, Employees.Init, Employees.AudID, tbl_Status.LtrDate, tbl_Status.FldDate, tbl_Status.RptDate, tbl_Status.ExamPer, tbl_Status.AcctgFees, tbl_Status.HW, tbl_Status.DBPen, tbl_Status.Appr, tbl_Status.Vac, tbl_Status.DCAnn, tbl_Status.VarAnn, tbl_Status.LMCC_NLMCC, tbl_Status.SAP, tbl_Status.NECA, tbl_Status.AMF, tbl_Status.NEBF, tbl_Status.LD, tbl_Status.Int, tbl_Status.TotDiscr, tbl_Status.AmtsColl, tbl_Status.ContribsPD, Employers.Location
FROM ((tbl_Status INNER JOIN Employers ON tbl_Status.EmpID = Employers.EmpID) INNER JOIN Employees ON tbl_Status.AudID = Employees.AudID) INNER JOIN JobType ON tbl_Status.TypeID = JobType.TypeID
ORDER BY tbl_Status.FundID, tbl_Status.YrSelect DESC , Employers.Employer, tbl_Status.RptDate;

When I try to run the procedure and click Debug it always stops on the line DoCmd.RunCommand acCmdPaste, the second line in the past section above.

I've tried all sorts of things but being an Access dummy not knowing precisely what I'm doing, I haven't been able to solve this one on my own.

Can someone help me out with this? Please remember I know just enough to be dangerous to myself and don't know all the technical jargon so try to keep any explanation/pointer/guidance simple.

Thanks in advance.

This is probably a simple fix, but I'm new to Access and pretty fried from doing a lot of work in an unfamiliar tool.

I have a massive system that I've developed for tracking inventory, and there's an "Items" table that needs to be added to. Certain columns contain the primary keys of other tables through a normal lookup type function.

I have a query that patches all this together, creating a table with all the text values rather than just the keys; however, I can't add to that table because I get the error "Cannot Add Record(s) - Join Key of [table] not in RecordSet." I've fiddled and searched but I haven't been able to come up with a solution.

I boiled down my problem to an ultra-simple database; it's attached here. You can duplicate the error by opening the only query and trying to add a record.

I'd appreciate assistance; thanks in advance!

Dan

testDB.accdb
testDB.mdb

I have a new build with a data entry form bound to a query which pulls from my "main" table and one linked table. The query pulls all the fields from the main table plus all the fields from the linked table. The primary keys for both tables are located in the query. The query runs fine.

I have one record in the tables which displays in the form. However, when I click to add a new record, I get the following error: "Cannot add record(s); Join Key of table "Main Table" not in recordset."

I've never run into this problem before...
Running Access 2007 in XP environment.

Thanks for the assist.

Can someone please explain what this message means. I am trying to add a record to table with a form. I am guess it has something to do with multiple tables the queries data comes from.

Cannot Add Record(s) Join Key of Table 'Delivery' not in Recordset

Is there another way to add a record through a form? Are Join not allowed when inserting new records?

I inhereted a database, and i know nothing about access. I'm trying to add a new payment record and I get this error: cannot add record(s); join key of table 'tblPayments' not in record set. I have never created or updated access before. Not sure how to fix this.

Mel

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.

i am trying to have a continuous form where i have a combo filter to filter the records. i would like to be able to add records on this form but i keep getting an error

Field cannot be updated

runtime error 3348
cannot add record(s); join key of table 'tblOrdersItems' not in record set.

the table system for the relevant tables is a one to many.(parent / Child)

it seems to work beautifully. i have the unique id fields of both tables in the form. i have the foreign key of the parent that is bound to the child form.

when i create a new record the error message appears. when i click end. i can see that the id fields have been populated and they look as they should. i cant seem to grasp what join key is not present.

ive uploaded a dummy to show you

open frmDiaryNoneItems. there is a combo box in the header that you will need to toggle to show some records. try and create a new record and you will grasp what i mean.

thanks

On a simple 2 table DB I was trying originally to use the form wizard, select all the fields from the first table and only selected fields from the second table. Then I would select the view on the second page of the wizard to show all the fields from both tables, without separating them into a subform. Then when I would try to test the form I would get the error message:

Cannot add record(s); join key of 'tblTwo' not in recordset

I knew it was because the autonumber field I was using as a primary key in the second table was purposely left off of the form when I created it. And I knew it had to have the record created by incrementing the autonumber field. I just didn't know how to do it behind the scenes. Is this where code on an OnExit click event would solve the problem? The only way I won't be new at this is if I get old at it

You're about to witness a grown man cry.

Thanks to this forum I've managed to put together my database. I believe it's fairly good (for me at least. I'm sure you Access MVPs could whip something twice as good in half the time!).

Anyway. My table done, I thought I'd design a form. Easy peasy!
So I thought.
Nothing has brought me quite as close to killing myself.

Let me give you a small example of my problem.

Tables:

tblCharity
CharityID
CharityName
TitlesID
FirstName
LastName
PositionID

tblTitles
TitlesID
Title

tblPosition
PositionID
Position

OK. So my relationships are all set up. Everything should work. I go into designing my form and that's where it all goes wrong. I'm going to take it slow and explain step by step. I'm not doing this because I think you're all as thick as I am (that's impossible) but because I want to make sure I explain it properly because the Samaritans are just about fed up with listening to me.

- I create a blank form.
- I insert the fields CharityName, FirstName and LastName.
- I go into Form View and check it works: it does. I try and enter some text. It does. I don't create any records though.

- On the right hand side of my window I see the Field list. Under "Fields avaliable in related tables" I see tblTitles and tblPosition. I click the little plus sign next to them and am shown the fields that those tables contain.

- I drag over Title and then Position.
- I go into Form View to test.
- I can enter information into the Title & Position fields. I cannot enter anything into the CharityName, FirstName and LastName. If I try to do so I am given the error message: "Cannot add record(s); join key of table 'tblCharity' is not in recordset'.

- I cry.

Now I have found that by using a subform I can input all the data needed but I think the subform layout is awfully ugly for something as simple as Title and Position (especially a "Mr" and then "Chairman" or something like that).

Now I've actually got 12 tables, but basically I need to know how I can put fields from different tables onto one form.

I hope I've explained that well. If not I'll zip up the DB and post it if it's easier.

Problem is I am inputing information to a form and I keep getting:

cannot add record(s) join key of table not in recordset

I know why it is having this problem. I have to hit save on the form to actually make my second table have an entry field made. I found this on the forums:

"Hard to explain quickly without knowing what your form does (or is meant to do), but basically it's this: you've got some Table1 and Table2 working together; they contain related data, and the way a db relates that data is on some key value, usually an ID field of some kind, a field common to both tables. Let's say Table1 is your master table (e.g. a customers table) and Table2 is dependent on it (e.g. an orders table). You can't add an order to Table2 unless the ID field is in the query, because you need to put a value in the ID field in order for the table to accept the data.

For more specific help, please tell us what your form is based on, and what it is meant to do, and look at the record source property of the form and let us know what that says."

Which is what is happening to me. My question is there a way I can redo this so I do not have to have to hit save in the middle of my form before I can complete the second half? If you can break it down Marine style it would be much appreciated.

Semper Fi

It's been a few months since I've used ACCESS and I've forgotten how to do something.

I have 2 tables:

tblClientInfoClientID (autonumber) (PK)
StreetAddress
City
StateProv
PostalCode
tblClientNameClientNameID (autonumber) (PK)
ClientID
ClientName
EffectiveDate
There is a 1 to many relationship between tblClientInfo and tblClientName, allowing for the database to track client name changes which may occur over time.

I want to create a Form to "Enter New Client Info" with the fields:ClientName
StreetAddress
City
StateProv
PostalCode
I don't really want to see sub-forms appearing on the form.

Problem: When I attempt to enter the ClientName (the first textbox), I get an error message: cannot add record(s); join key of table 'tblClientName' not in recordset.

Any suggestions?

Thx.







I try to add data to a form but the following message comes up;
cannot add record(s);join key of table 'tbl_receipts'not in recordset

I have an 'add new order' form which opens with all the fields blank and focus set to a combo box. The first thing the user must do is select a contract name or number from an unbound combo box. I then want the form to update and show all the relevant information in the form fields.

In the row source of the combo box I select all 3 fields from the same table (Project_Info). Only the contract number is visible all others are set to 0cm width in combo box properties.

In the afterupdate event I have the following code to set the field to the values from the select query

My code looks like this:

Private Sub ProjectContractNum_AfterUpdate()
Dim sOrder_idSource As String
Dim rs As Object
ProjectName = ProjectContractNum.Column(1)
proj_status = ProjectContractNum.Column(2)

End Sub
[end code]

My SQL in rowSource looks like this:
SELECT Project_Info.proj_name, Project_Info.proj_contract_no, Project_Info.proj_status FROM Project_Info;

Access tells me that I don't have a join key in my record set.
message reads as follows:-
Run-time error '2147352567 (80020009)':
Cannot add record(s);Join key of table Project_Info' not in record set.

Debug highlights this line:
proj_status = ProjectContractNum.Column(2)

'proj_status' is the name of the text box and the control source.


If I just use just the first 2 fields (project number and project name) these two work!!

What am I doing wrong?

Thx, Kev.

Hello I Have created a form with multiple subforms in it. whenI try to created a new record using the subforms I get the error " Cannot add record(s); join key of table not in result set. (Error 3348)". If someone could help me fix this it would be greatly apperciated. Thanks

Hi all,

I am working on a project that requires me to develop a MS Access database for a motor accident company. I have created the tables and added foreign keys to relate them (not sure if they are correct). I have not defined any relationships in the relationships window. I then created a form from multiple tables so that I could start inserting data into the database. When I tried to insert data into the text boxes in the form I got the message in the tool bar at the bottom of the page, "Cannot add record(s); join key of table DriverDetails not in recordset".

Is the above problem caused by me not relating the tables correctly? I have no data in my tables yet because I was going to use the form to start inserting the data. Is this correct? I feel like I’m blindly struggling through this and am in need of some direction, can anyone help?

Many thanks

Neale

Ave created forms etc from tables, within a sub form i am trying to enter data but its giving me the following error when i try to enter data into the role no, field. "Field cannot be updated"

Then if i go to the next field in the subform and try to enter data its coming up with the following error,

"Cannot add record(s); join key of table 'AC_ROLE' not in recordset"

Can any one suggest possible solutions!

Relationships and Queries

I created a simple 3 table relationship. The third table is a junction table. All three are related to each other. Now I built a query for a subform. I put the subform into my main form. I went to add data into the subform and this error popped up:

Cannot add record(s) join key of Table " " not in recordset.

What do I need to do from here?

Thanks.

j

Hello I am new to access development.
I read several of the entries similar to my error:
Cannot add record(s);join key of table 'dboAssets' not in recordset.

The information was previuosly in one record name inventory. This was not normalized so following a books advice, I did. Now i am having a good deal of time putting the information back together. I moved all the information gathered into the inventory table. When I try and create the form using the form wizard it creates a subform and I need it all in one form. I can recreate the nice form, but am not getting what I missed in the join. When I preview the sql view there are lots of fields that are not in my Assets table. This is more complicated than my book explained. Any assistance would be appreciated.

I have created a Combo lookup box as many times in the past.

The control on a form looks up a Customers Table and lists the CustomerID & Customer Nane, and displays them in the drop down box.
So far so good!!!

However, when I select a Customer, I get an Error Message "Cannot add record(s); join key of table 'TABLENAME' not in recordset"; but as far as I can see all relationships, field propeties, etc are correct. The field in the Customer table is 'ACNO' text, 8 chr, and in the 'OTHERTABLE' 'CustID' text, 8 chr.
(The form has a query as its recordsource)

The 'ACNO' is of the form 'ABC1234'.

Where is the error PLEASE??

[This message has been edited by EricH (edited 05-30-2001).]

[This message has been edited by EricH (edited 05-30-2001).]

hi

i've got my form up and running, kind of. something's happened to my form since i worked on it two months ago.

when trying to add records into a subform on the main form, i keep getting the error message "Cannot add record(s); join key of table "TblUnitEnrolment" not in recordset."

can anyone help..thanks

trish

**Overall Problem**

I'm trying to create an inventory database that you can check items out to the people associated with the database but I can't figure out how to create the form to do this. Any help would be greatly appreciated.

**Background information**

First, here are the associated data structures:

>TablesQueries

Hi guys,

I've managed to start a few tables, a log in form and a little flash screen. I then created a form that will serve us a fill-up form.

But when I tried to input data to my "Location of Incident, Time, Date , Narrative and Resolution fields it doesn't allow me to and displays an Error message saying Cannot add record; join key of table "tblIncident Info" not in recordset.

To what I see, I think I mess up with my relationships. Or I don't know.

I would be willing to provide a link to my relationships field but i'm still not allowed to do so. I've already check the post by Nano but to no avail I can't find the solution to my problem there.

Hope someone can help me with this.

EDIT:

What i want to do is that there would only be ONLY one Form with tabs and that a user will just input the data on the fields provided. Is that advisable? Or would it be better if there are multiple forms?

I also already did several revisions and now on this current one I've decided to use a design concept from databaseanswers since it's closely the same design to what i have in mind.

I will post a zip file of my db later.


Not finding an answer? Try a Google search.