Share via

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
  1. 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!

    2 people found this answer helpful.
    0 comments

40 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-07-18T13:15:42+00:00

    What are the names and datatypes of the relevant fields (especially the Primary Key fields) in the two tables? How (if at all) are the two tables related in the Relationships window?

    SimpleEmployees table:

    ID=AutoNumber (PK), SSN=Text, Bank Account #=Text, First Name=Text, MI=Text, Last Name=Text, Company Name=Number (lookup: RowSource: SELECT [SimplePlanBusinesses].[ID], [SimplePlanBusinesses].[CompanyName] FROM SimplePlanBusinesses ORDER BY [CompanyName]; )

    SimplePlanBusinesses table:

    ID=AutoNumber (PK), CompanyName=Text, Company address lines are all text, Plan Type is a lookup...

    Relationships window has a relationship between SimpleEmployees CompanyName and SimplePlanBusiness ID (I couldn't tell you what type.....I just have a line between the two tables)

    0 comments
  2. Anonymous
    2013-07-17T21:47:37+00:00

    The join and the likely problem is in the bolded section:

    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]));

    Since I cannot see your table design and do not know the names or datatypes of the relevant fields, it's hard to be sure; but going by the fieldnames, it appears that you're trying to link a SimplePlanBusinesses ID field (which I would guess would be an Autonumber) to the SimpleEmployees CompanyName field. Unless you have companies named 31 or 582, it's more likely that CompanyName is a Text field and therefore cannot be linked!

    What are the names and datatypes of the relevant fields (especially the Primary Key fields) in the two tables? How (if at all) are the two tables related in the Relationships window?

    0 comments
  3. Anonymous
    2013-07-17T21:33:45+00:00

    I changed the relationship to the ID. That didn't fix the issue. Here is the SQL after that change (not sure where to look for change):

    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]));

    0 comments
  4. Anonymous
    2013-07-17T21:17:55+00:00

    Is [CompanyName] a Lookup field? If so you can't join on the name itself, only on the numeric ID field that the Lookup Wizard conceals from your view.

    0 comments