Try using an inner join. Queries with unjoined tables are not updateable.
SELECT tblScores.*, tblStudents.Personal_Goals
************************************************************************
FROM (tblPeriods INNER JOIN tblScores
ON tblPeriods.Period_Code = tblScores.Period_Code)
************************************************************************
INNER JOIN tblStudents ON tblScores.Student_ID = tblStudents.Student_ID
WHERE [tblScores].[Student_ID]=[Forms]![frmStudentScoreEntry]![txtStudent_ID]
And [tblScores].[Score_Date]=[Forms]![frmStudentScoreEntry]![txtScore_Date]
This still may not be updateable.
In Access Help type the following in the Answer Wizard tab
When can I update data from a query
In the results window select About Updating Data and select the appropriate
option.
If you cannot edit the data in a query, this list may help you identify why it
is not updatable:
--Query based on three or more tables in which there is a many-to-one-to-many
relationship
--Query that includes a linked ODBC table with no unique index or a Paradox
table without a primary key
--Query that includes more than one table or query and the tables or queries
aren't joined.
--It has a TRANSFORM clause. A Crosstab query is always read-only.
--It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries
that aggregate records are read-only.
--It has a GROUP BY clause. A Totals query is always read-only.
--It contains a DISTINCT predicate (i.e.; Unique Values is YES in the query's
properties.)
--It involves a UNION. Union queries are always read-only.
--It has a subquery in the SELECT clause.
--It uses JOINs of different directions on multiple tables in the FROM clause.
--The fields in a JOIN are not indexed correctly: there is no primary key or
unique index on the JOINed fields.
--The query's Recordset Type property is Snapshot. Set Recordset Type to
"Dynaset" in the query's Properties.
--The query is based on another query that is read-only (stacked query.)
--Your permissions are read-only (Access security.)
--The database is opened read-only, or the file attributes are read-only, or
the database is on read-only media (e.g. CD-ROM, network drive without write
privileges.)
-- Access 2007: The query calls a VBA function, but the database is not in a
trusted location so the code cannot run.
Also see
http://allenbrowne.com/ser-61.html
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
On 5/17/2011 9:26 AM, LAS wrote:
> I have a form with the following record source. I can update columns from tblScores just fine.
>
> SELECT tblScores.*, tblStudents.Personal_Goals
> FROM tblScores INNER JOIN tblStudents ON tblScores.Student_ID = tblStudents.Student_ID
> WHERE (((tblScores.Student_ID)=[Forms]![frmStudentScoreEntry]![txtStudent_ID]) AND ((tblScores.Score_Date)=[Forms]![frmStudentScoreEntry]![txtScore_Date]));
>
> But when I add the table in red, below, I can no longer update. No change is made, I get a beep, and "This recordset is not updateable" appears at the bottom of the form. I want to add the table just so I can order the rows in the form. But I don't need the Order By clause to cause the problem.
>
> SELECT tblScores.*, tblStudents.Personal_Goals
> FROM tblPeriods, tblScores INNER JOIN tblStudents ON tblScores.Student_ID = tblStudents.Student_ID
> WHERE (((tblPeriods.Period_Code)=tblScores.Period_Code AND ((([tblScores].[Student_ID])=[Forms]![frmStudentScoreEntry]![txtStudent_ID]) And (([tblScores].[Score_Date])=[Forms]![frmStudentScoreEntry]![txtScore_Date]))));
>
> What is the rule that makes this not updateable? Is there any way I can order by form rows by a value in the tblPeriods table?
>
Similar Threads
