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

Anonymous
2013-07-17T21:12:56+00:00

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!

Microsoft 365 and Office | Access | For home | Windows

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
2013-07-23T20:42:50+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

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!

Was this answer helpful?

2 people found this answer helpful.
0 comments

40 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-07-18T17:30:55+00:00

    It is totally unclear which table you are entering data into or what data you are entering. But as I said, you don't need to include both tables in the Recordsource.

    --->     I'm entering SimpleEmployee data into the SimpleEmployee table. The employee list being viewed is base on the link between the Employee and Company tables to be able to view only the employees that share the same employer/company.

    My blog shows different ways around this.

    --->     not sure where to look, what to look for to find help on your blog for this.....

    Was this answer helpful?

    0 comments
  2. ScottGem 68,845 Reputation points Volunteer Moderator
    2013-07-18T17:17:14+00:00

    Again, if you are not entering data into both tables, there there is no reason to join then in the Recordsource. 

    It is totally unclear which table you are entering data into or what data you are entering. But as I said, you don't need to include both tables in the Recordsource. My blog shows different ways around this.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-07-18T17:06:30+00:00

    Generally, a multi-table query will be uneditable. If you are trying to enter data into 2 different tables then use a main form/sub form combination. 

    I'm not trying to enter into two tables, I'm only trying to enter into one table that has a link to another (Employer/Company Name) I was just using this form as a way around selecting the employer/company name.....

    Desired effect:

    select employer/company name, list of employees shows in a list of some sort, using fields on main form-employee data is editable/deletable and an employee can be added without needing to select employer/company name (altho not a big deal if we have to).

    Was this answer helpful?

    0 comments
  4. ScottGem 68,845 Reputation points Volunteer Moderator
    2013-07-18T16:50:21+00:00

    Generally, a multi-table query will be uneditable. If you are trying to enter data into 2 different tables then use a main form/sub form combination. 

    If you are trying to enter a foreign key, then you do not need to join the table, just set the Rowsource of a combo to lookup to the table.

    Was this answer helpful?

    0 comments