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: Newest
Most helpful Newest Oldest
  1. Anonymous
    2013-07-19T18:59:37+00:00

    Who said that? It is not only desirable, but (IMHO) mandatory that a lookup be done to make sure that input is standardized. But the lookups should be done with list controls on a form, NOT with a lookup field on the table level. Like I said; "You then use a combobox on the form to lookup the company."

    This is a major part of what is tripping you up. You think that a lookup has to be done on the table level, and that means you have to include the lookup field on your form, and that requires a join. But that's not the case. Foreign Keys are almost always filled in using a list control. But, again, on the form, not in a table. And by using a list controls there is no reason to join to the table you are looking up to.

    John: "A number is not a text string. The LOOKUP WIZARD often causes this confusion; if you look at the table you see a text company name, but the table doesn't actually CONTAIN a text company name! The lookup wizard puts in a combo box control which actually stores a number, but what you SEE is not that number, but a text lookup field. That's why a lot of us really dislike the lookup wizard!"

    I guess I may have read into that a bit further than I should have and was not corrected until now...

    *So rather than a lookup on a table, it should be on the form-right? Does the table then contain the ID in place of the lookup?

    Was this answer helpful?

    0 comments
  2. ScottGem 68,845 Reputation points Volunteer Moderator
    2013-07-19T18:21:57+00:00

     If it is not desireable to use a lookup to make sure the list of company names are the same on both tables, how is this accomplished?

    Who said that? It is not only desirable, but (IMHO) mandatory that a lookup be done to make sure that input is standardized. But the lookups should be done with list controls on a form, NOT with a lookup field on the table level. Like I said; "You then use a combobox on the form to lookup the company."

    This is a major part of what is tripping you up. You think that a lookup has to be done on the table level, and that means you have to include the lookup field on your form, and that requires a join. But that's not the case. Foreign Keys are almost always filled in using a list control. But, again, on the form, not in a table. And by using a list controls there is no reason to join to the table you are looking up to.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-07-19T17:29:13+00:00

    As we have tried to explain to you, it is not necessary to link the tables for purposes of this form. I am still unclear here of the purpose of this form and this table. 

    *The table is used to hold all employee personal data, the form is used to view/edit that data based on the employer/company

    If this is the main employee table then you should have a separate company table and rather than a company name field, you should have a company ID field as a foreign key. You then use a combobox on the form to lookup the company.

    *I DO have a separate company table. The company name field on the employee table is a lookup to that separate table.

     If it is not desireable to use a lookup to make sure the list of company names are the same on both tables, how is this accomplished?

    Was this answer helpful?

    0 comments
  4. ScottGem 68,845 Reputation points Volunteer Moderator
    2013-07-19T17:11:48+00:00

    how do I accomplish the linking of the two tables based on this field? I used the list of company names from the company table as the list of options for employer in the employee table so I would not run into issues of misspelling and such....

    As we have tried to explain to you, it is not necessary to link the tables for purposes of this form. I am still unclear here of the purpose of this form and this table. 

    If this is the main employee table then you should have a separate company table and rather than a company name field, you should have a company ID field as a foreign key. You then use a combobox on the form to lookup the company.

    If this is NOT the main employee table, then you should only have the EmployeeID as a foreign key. You should not be repeating employee name and other info in this table.

    Was this answer helpful?

    0 comments