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-19T19:06:44+00:00

You should be able to open the table in design view and get rid of the lookup; select the Company field and change the "Display COntrol" line on the Lookup tab to Text Box.

And yes, the table already contains the ID (even though that simple fact is concealed from view by the infuriating wizard), and yes, the form should have a combo box bound to that numeric ID field. The form combo box would be based on a two column query with the company ID as the bound column, and the company name as a second column; the ColumnWidths property would be set to something like

0";1.25"

to conceal the ID but display the company name.
Was this answer helpful?

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

2013-07-19T19:28:13+00:00

Yes, as John has already said, the Rowsource of the lookup field, should actually be the same as the Rowsource for the combo on the form.

The field in your table is a Foreign Key and contains the ID value of the record in the lookup table.
Was this answer helpful?

0 comments No comments
Anonymous

2013-07-23T15:24:01+00:00

You should be able to open the table in design view and get rid of the lookup; select the Company field and change the "Display COntrol" line on the Lookup tab to Text Box.

And yes, the table already contains the ID (even though that simple fact is concealed from view by the infuriating wizard), and yes, the form should have a combo box bound to that numeric ID field. The form combo box would be based on a two column query with the company ID as the bound column, and the company name as a second column; the ColumnWidths property would be set to something like

0";1.25"

to conceal the ID but display the company name.

Okay, ran into another snafu.... got the table fixed, the combo box for selecting the company working beautifully, now I can't get my queries to run properly that are based on the company selected in the combo box....

there has to be a simple setting somewhere that just isn't jumping out to me....
Was this answer helpful?

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

2013-07-23T16:24:34+00:00

Okay, ran into another snafu.... got the table fixed, the combo box for selecting the company working beautifully, now I can't get my queries to run properly that are based on the company selected in the combo box....

there has to be a simple setting somewhere that just isn't jumping out to me....

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,
Was this answer helpful?

0 comments No comments