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. Anonymous
    2013-07-18T20:47:34+00:00

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

    What I currently have:

    *A form based on Company Table with ComboBox to select Company.

    *A button that opens another form that uses the Company selected as criteria. This form is the list of Employees associated with the Company that was selected in the first form.

    *Edit and Delete work on this form (which is a split form to have all data on top and a table view below). I cannot add to this list in any way from this form. This is my issue.

    Was this answer helpful?

    0 comments
  2. ScottGem 68,845 Reputation points Volunteer Moderator
    2013-07-18T20:54:52+00:00

    What I currently have:

    *A form based on Company Table with ComboBox to select Company.

    *A button that opens another form that uses the Company selected as criteria. This form is the list of Employees associated with the Company that was selected in the first form.

    *Edit and Delete work on this form (which is a split form to have all data on top and a table view below). I cannot add to this list in any way from this form. This is my issue.

    Ok, so when you open the second form (after selecting a company) the form is filtered for for that company? How are you doing this? What is the code behind button?

    Also, did you check to make sure Allow Additions is set to True?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-07-18T21:02:27+00:00

    What I currently have:

    *A form based on Company Table with ComboBox to select Company.

    *A button that opens another form that uses the Company selected as criteria. This form is the list of Employees associated with the Company that was selected in the first form.

    *Edit and Delete work on this form (which is a split form to have all data on top and a table view below). I cannot add to this list in any way from this form. This is my issue.

    Ok. You are clearly committed to doing this the hard way.

    Again let me ask:

    Have you even TRIED the very simple option of making the Employee form a SUBFORM of the Company form, using the CompanyID as the master/child link field?

    If you do so you need NO code; the subform will be editable; the company information and the employee information will both be visible at the same time; the form wizard will help you build it.

    If you have rejected this alternative, I'd be curious to know why; 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. The reason you're unable to add a record is that since your query has two tables, Access thinks you need to add a new company AND a new employee (two new records to two different tables). You don't, of course, but because of the way you set up the form, Access thinks that's what you want!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-07-18T21:46:15+00:00

    Ok. You are clearly committed to doing this the hard way.

     

    Again let me ask:

     

    Have you even TRIED the very simple option of making the Employee form a SUBFORM of the Company form, using the CompanyID as the master/child link field?

     

    If you do so you need NO code; the subform will be editable; the company information and the employee information will both be visible at the same time; the form wizard will help you build it.

     

    If you have rejected this alternative, I'd be curious to know why; 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. The reason you're unable to add a record is that since your query has two tables, Access thinks you need to add a new company AND a new employee (two new records to two different tables). You don't, of course, but because of the way you set up the form, Access thinks that's what you want!

    I'm not doing it the "hard way" just because.

    I have several forms, reports and mail merges that all run off of the one company selection form.

    I will try changing record source from the query to the table...

    Was this answer helpful?

    0 comments