cannot add record, join key of table is not in recordset

Anonymous

I have a split form that is based on this query:

SELECT SimpleEmployees.SSN, SimpleEmployees.[First Name], SimpleEmployees.[Last Name], SimplePlanBusinesses.CompanyName, SimpleEmployees.[Bank Account #], SimpleEmployees.[Employee Deferral %], SimpleEmployees.[Employer Deferral %]

FROM SimplePlanBusinesses INNER JOIN SimpleEmployees ON SimplePlanBusinesses.ID = SimpleEmployees.[CompanyName]

WHERE (((SimplePlanBusinesses.CompanyName)=[Forms]![CompanySelection]![CompanyName]));

on a prior form I select the company and at a button click this split form opens. I have a button that will bring me to a new blank field for entry of a new employee in that particular company. I have recently discovered that I cannot add a new employee in this form as I had originally intended. it gives me the error of my title.

any thoughts?

TIA!

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

Anonymous

and.... I have solved the issue! Had to change the query to this form to be based ONLY on the one table.

...still working on getting the Company Name (which is listed only in the Company table) to show up somewhere--trying for the header right now--just so the user knows what company they are looking at the employees of rather than just a number......

AND a way to have the "Company Name" field to "auto enter" on the form so a user cannot enter anything that would mess up the table

Well, as I said back on July 19:

"Your query is causing problems - as we've said repeatedly - because it joins two tables. You're not USING any of the SimplePlanBusinesses fields in the form - so JUST LEAVE OUT THE OTHER TABLE!!! "

Others of us said it too. Sometimes we actually mean what we post...!!!!!

And to display the company name on the Subform (which with a well designed form shouldn't be needed, as it would already be visible on the Mainform), you can change the Textbox showing the CompanyID from a Textbox to a Combo Box. The combo box's properties would resemble:

ControlSource - CompanyID

RowSource - SELECT Companies.CompanyID, Companies.CompanyName FROM Companies ORDER BY CompanyName;

ColumnCount - 2

ColumnWidths - 0";2"

Enabled - No (This will keep the user from inappropriately changing the company on the subform)

Locked - Yes (this will also keep the user from changing but will turn off the "greyed out" look)

If the Master Link Field and Child Link Field properties of the Subform are correct (CompanyID) then it will indeed "auto enter" the CompanyID from the mainform into new records on the subform, and will correctly display that company. It's builtin to the Subform mechanism; you don't need any code or fancy shenanigans to get it to do this!

0 comments

40 additional answers

Anonymous

2013-07-24T13:58:19+00:00

it is not a main and subform thing, it is two different pages...

either way, I just needed fresh eyes to try the combo box thing again. So far it appears to be working.

Thank you for putting up with my daftness.

Any idea of how to make the arrow of the combo box go away? is it possible? I am not sure I'm a fan of the fact that it looks like you can change the employer in that manor....

thanks again.
Was this answer helpful?

0 comments No comments
ScottGem 68,845 Reputation points Volunteer Moderator

2013-07-23T20:44:46+00:00

and.... I have solved the issue! Had to change the query to this form to be based ONLY on the one table.

...still working on getting the Company Name (which is listed only in the Company table) to show up somewhere--trying for the header right now--just so the user knows what company they are looking at the employees of rather than just a number......

AND a way to have the "Company Name" field to "auto enter" on the form so a user cannot enter anything that would mess up the table

Which is what we told you in the beginning, not to base your form on a multi-table query.

Do the same thing that we have told you to do with comboboxes. Your CompanyID field should be bound to a combobox not a text control. The Rowsource of that combo is the company table.
Was this answer helpful?

0 comments No comments
Anonymous

2013-07-23T18:54:49+00:00

**back to the same issue of not being able to enter data into my form because "join key of table is not in recordset" :(

and.... I have solved the issue! Had to change the query to this form to be based ONLY on the one table.

...still working on getting the Company Name (which is listed only in the Company table) to show up somewhere--trying for the header right now--just so the user knows what company they are looking at the employees of rather than just a number......

AND a way to have the "Company Name" field to "auto enter" on the form so a user cannot enter anything that would mess up the table
Was this answer helpful?

0 comments No comments
Anonymous

2013-07-23T18:44:59+00:00

Yes, its because you aren't matching the data properly. Your combobox is storing The ProductID, NOT the Product name So you need to set the criteria in your query on the ProductID column,

Just realized after your tip fixed my issue with the queries not working that the ID and Name you have listed have nothing to do with my database of Companies and Employees.....

oh well :)

**back to the same issue of not being able to enter data into my form because "join key of table is not in recordset" :(
Was this answer helpful?

0 comments No comments