Recordset not updateable

L

LAS

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?
 
J

John Spencer

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

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?
Click to expand...
 
L

LAS

Thanks so much!!! That did it! And thanks for sending the results of the
Answer Wizard tab. I'm running 2007, but in 2003 format. If I've got it, I
can't find the "Answer Wizard Tab."

John Spencer said:
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

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?
Click to expand...
Click to expand...
 
J

John Spencer

Just enter the phrase in the help search.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Thanks so much!!! That did it! And thanks for sending the results of the
Answer Wizard tab. I'm running 2007, but in 2003 format. If I've got it, I
can't find the "Answer Wizard Tab."

John Spencer said:
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

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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

What makes a query updateable? 2
Table changes cause "the recordset is not updateable???" 4
A way to find "no match" when two columns needed for linking? 6
Has this ever happened to you? 4
Reset a recordset? 2
Access behaves differently in different environments 3
RecordsetNot Updateable 22
Ambiguous outer join 9

Top