PC Review


Reply
Thread Tools Rate Thread

Recordset not updateable

 
 
LAS
Guest
Posts: n/a
 
      17th May 2011
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?

 
Reply With Quote
 
 
 
 
John Spencer
Guest
Posts: n/a
 
      17th May 2011
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?
>


 
Reply With Quote
 
 
 
 
LAS
Guest
Posts: n/a
 
      17th May 2011
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" <(E-Mail Removed)> wrote in message
news:iqu6id$lsk$(E-Mail Removed)...
> 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?
>>

>



 
Reply With Quote
 
John Spencer
Guest
Posts: n/a
 
      18th May 2011
Just enter the phrase in the help search.


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

On 5/17/2011 3:40 PM, LAS wrote:
> 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"<(E-Mail Removed)> wrote in message
> news:iqu6id$lsk$(E-Mail Removed)...
>> 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?
>>>

>>

>
>
>


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error 3326: This Recordset is not Updateable slim Microsoft Access 2 13th Oct 2006 02:58 PM
Updateable query changes to non-updateable rohandawate@gmail.com Microsoft Access Getting Started 1 18th Aug 2005 08:30 PM
Recordset Not Updateable Graham Microsoft Access 0 10th Mar 2004 03:10 AM
Recordset not Updateable =?Utf-8?B?QW15?= Microsoft Access 3 5th Dec 2003 11:31 PM
Help wanted: 'Recordset not updateable' ren Microsoft Access 3 7th Nov 2003 10:38 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:45 AM.