Subform Restrictions

Posted on 2011-05-06
Medium Priority
Last Modified: 2012-05-11
In Access I had designed a form (projects) with a subform (people).  The subform would allow you to add existing people or enter a new person. When I converted the application to SQL Server backend, I could no longer add new people on the subform only add existing people.

Main Form
Project ID
Project Name

People Subform
People ID
People Name

Because projects can have many people and people can be on many projects there is a junction table


Why would SQL Serve database now not allow me to add new people on a subform when Access would?
Question by:gcgcit
1 Comment
LVL 11

Accepted Solution

LambertHeenan earned 2000 total points
ID: 35708658
I'm guessing that in your junction table "ProjectID" is the foreign key into the projects table, and likewise "PeopleID" is the FK to your people table.

So both those fields in the junction table need to allow duplicate values as people can be involved with many projects.

So the problem you have is that the junction table does not have a unique index, and for a table to be updateable in SQL server there must be such a unique index. So the problem should be solved by simply adding an INDENTITY field to the junction table (I think that's the right term, not using SQL server myself.)

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question