TR Forums

Posted: **Wed Mar 25, 2009 9:44 pm**

Need a little help here on Access:

I have 2 tables:

Table_Parent with fields:
Order (Primary Key)
AccountID

Table_Child with fields:
AutoNumber ID (Primary Key)
Order
Cost
LocationID

I made a main form that had a combo-box filter (LocationID) to display the proper information in a sub-form.

The sub-form had a recordsource as follows:

"SELECT * FROM Table_Parent INNER JOIN Table_Child ON Table_Parent.Order = Table_Child.Order WHERE Table_Child.LocationID = '" & cboLocation & "'"

Now the form works visually, I pull down the combo-box with a LocationID and the sub-form displays the specified fields for that LocationID.

However if I try to add a new record I get the message:

Cannot add records(s); join key of table 'Table_Child' not in recordset.

Note: My Link Master Field and Link Child Fields are completely blank right now in the properties page.

Posted: **Wed Mar 25, 2009 10:12 pm**

I don't have Access experience.. but the error explanation should give a clue.
firstly I have never seen a JOIN expression used when inserting records, so that could be a mistake, but its more likely to just be outside my experience.

secondly, if you are doing a "JOIN"ed insert, perhaps you need to be careful about the IDs that you are "JOIN"ing on - amybe there's a conflict there.
Are you using Foreign keys or anything outside the most basic linked tables setup? (can you even do Foreign Keys with Access?)

have you tried doing a Google on ""join key of table 'Table_Child' not in recordset."" ?

in all of the above I have assumed that the error message is whinging about a JOIN portion of the insert statement, which might not be true!

can you capture the exact SQL syntax line which produces hte error?

Posted: **Wed Mar 25, 2009 10:16 pm**

theofficeexperts.com wrote:
Q: I am trying to add records in a form (i let the wizards do everything, learning process, you know) and there is a message in the bottom left of the screen saying "cannot add records;join key of table not in recordset"-is there a quick explanation of what i need to do here?????

A: 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.

that looks like it should help.

Posted: **Wed Mar 25, 2009 10:31 pm**

Can you post the Insert query that is giving the error?

I see you are using a common value which is Order.

I really don't think you can use a Join in an Insert statement by the way.

Posted: **Thu Mar 26, 2009 7:04 am**

reli4nt wrote:
Can you post the Insert query that is giving the error?

I see you are using a common value which is Order.

I really don't think you can use a Join in an Insert statement by the way.

I didn't use any specific Insert Query, the Sub-Form (Table_Child) is in a spreadsheet-view and I simply select a new row to make a new record.

Here's the kicker: The Sub-Form has only one column for "Order" which I wanted to populate both tables at the same time when I enter in data (Table_Parent and Table_Child). I also wanted the LocationID to be autofilled on Table_Child based upon the selection I made on the Location combo-box. So if LocationID is "1" on the Combo-box, any records I attempt to enter in on the Sub-Form should automatically populate in the Table_Child with LocationID = "1".

Again I am wondering if I should utilize Link Master Field and Link Child Field (both currently blank) set to "LocationID" to make this work?

Posted: **Thu Mar 26, 2009 5:28 pm**

NeRve wrote:
reli4nt wrote:
Can you post the Insert query that is giving the error?

I see you are using a common value which is Order.

I really don't think you can use a Join in an Insert statement by the way.

I didn't use any specific Insert Query, the Sub-Form (Table_Child) is in a spreadsheet-view and I simply select a new row to make a new record.

Here's the kicker: The Sub-Form has only one column for "Order" which I wanted to populate both tables at the same time when I enter in data (Table_Parent and Table_Child). I also wanted the LocationID to be autofilled on Table_Child based upon the selection I made on the Location combo-box. So if LocationID is "1" on the Combo-box, any records I attempt to enter in on the Sub-Form should automatically populate in the Table_Child with LocationID = "1".

Again I am wondering if I should utilize Link Master Field and Link Child Field (both currently blank) set to "LocationID" to make this work?

hooly dooly.. that sounds like Japanese. I think you'll need some Access specific help there.
My only concern would be if the "Autofilled" column/field is equivalent to an "auto_increment" field, and therefore expecting it to be set based on what you click on is a mistake.

Posted: **Thu Mar 26, 2009 6:28 pm**

No the autofill I'm talking about is not an autoincrement. It automatically fill in the specific LocationID for a new record.

For example, if I select LocationID = "1" on the combo-box, it will show all records with LocationID = "1".
I want to add a new record and I go to a new row in the spreadsheet and start typing in stuff (making a new record) - Access automatically assumes that new record as having field value of LocationID = "1" (since I filtered it that way).

TR Forums

Adding Record On Query Generates "Cannot Add Record"

Adding Record On Query Generates "Cannot Add Record"

Re: Adding Record On Query Generates "Cannot Add Record"

Re: Adding Record On Query Generates "Cannot Add Record"

Re: Adding Record On Query Generates "Cannot Add Record"

Re: Adding Record On Query Generates "Cannot Add Record"

Re: Adding Record On Query Generates "Cannot Add Record"

Re: Adding Record On Query Generates "Cannot Add Record"