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-19T13:33:25+00:00
If that doesn't help or doesn't make sense... 1. Please post the code you're using to open the form. Are you using VBA code or a macro? 2. This isn't a Web form is it?

You could base the second form on a Query (just on the employee table, not both tables) referencing the first, using a criterion on the CompanyID (not the company name!!!)
1. I'm using a macro: OpenForm, Form Name: SimpleEmployees Add/Edit, View: Form, Window Mode: Normal
1. Since I have no idea what a Web form in Access is, I'm hoping it is not.
2. I tried changing the query to be based off of just the SimpeEmployee table and use the criteria of the form and the error that pops up is:
*This expression is typed incorrectly, or it is too complex to be evaluated. For example a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

-not sure how to base on ID rather than name since the SimpleEmployee table field with the Company is a lookup rather than a stand-alone value.....

Now I'm really lost......
Was this answer helpful?

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

2013-07-18T22:23:46+00:00

since I do not have the builder option for a filter on load, how do I limit my table to just the employees who share the company selected in the first form?

To be able to answer that we need to know what code you are using to load that form.
Was this answer helpful?

0 comments No comments
Anonymous

2013-07-18T22:08:23+00:00

but all I can suggest is that you need to change the Recordsource property of your second form to be based just on the Employee table rather than basing it on a query joining both tables.

since I do not have the builder option for a filter on load, how do I limit my table to just the employees who share the company selected in the first form?

You could base the second form on a Query (just on the employee table, not both tables) referencing the first, using a criterion on the CompanyID (not the company name!!!) such as

=Forms![NameOfFirstForm]![NameOfCompanyIDcontrol]

If that doesn't help or doesn't make sense... Please post the code you're using to open the form. Are you using VBA code or a macro? This isn't a Web form is it?
Was this answer helpful?

0 comments No comments
Anonymous

2013-07-18T21:50:59+00:00

but all I can suggest is that you need to change the Recordsource property of your second form to be based just on the Employee table rather than basing it on a query joining both tables.

since I do not have the builder option for a filter on load, how do I limit my table to just the employees who share the company selected in the first form?
Was this answer helpful?

0 comments No comments