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: Oldest
Most helpful Newest Oldest
  1. ScottGem 68,845 Reputation points Volunteer Moderator
    2013-07-18T18:14:29+00:00

    I'm still not clear here. Are you trying to restrict the form to only show employees in a specific company? If so, then you should filter the form for that company.

    Did you read the blog in question?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-07-18T18:35:01+00:00

    I'm still not clear here. Are you trying to restrict the form to only show employees in a specific company? If so, then you should filter the form for that company.

    Did you read the blog in question?

    I opened the blog and did a quick scan. I don't have time to sit and read all of the entries if they do not pertain to this issue... I've got other work to do.

    Like I said, I don't even know what I'm looking for... a title or heading would help me.

    As far as a filter goes, I thought that was what my combobox was doing............

    Was this answer helpful?

    0 comments
  3. ScottGem 68,845 Reputation points Volunteer Moderator
    2013-07-18T20:09:36+00:00

    Here is the thing. What you appear to want to do is enter data into one table using a form. However, you want to be able to display data from a related record in another table on the same form. 

    If that is what you want to do, then the blog article shows you several ways to that. If that is not what you want, then you don't need the article.

    If you just want to restrict records that the form displays, then you use a filter.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-07-18T20:27:51+00:00

    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).

    The simplest way to do this is to use a Form based on the company table (with a Combo Box on the form to select which company; the toolbox combo wizard will help you create such a combo box), with a Subform for the employees.

    If you have an enforced relationship between the tables so that each Employee must be associated with one and only one Company, then you can't just add an employee without selecting the company. Using the subform makes this simple and automatic (adding a new employee will automatically include the company ID of the record on the parent form).

    If you have chosen NOT to do this, and instead want to base the form on a two table query joining the company and employee tables, it's still possible but it's a lot more work; could you explain why you made that choice?

    Was this answer helpful?

    0 comments