Recordset not updateable

Discussion in 'Microsoft Access Queries' started by LAS, May 17, 2011.

  1. LAS

    LAS Guest

    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?
     
    LAS, May 17, 2011
    #1
    1. Advertisements

  2. LAS

    John Spencer Guest

    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?
    >
     
    John Spencer, May 17, 2011
    #2
    1. Advertisements

  3. LAS

    LAS Guest

    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" <> wrote in message
    news:iqu6id$lsk$...
    > 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?
    >>

    >
     
    LAS, May 17, 2011
    #3
  4. LAS

    John Spencer Guest

    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"<> wrote in message
    > news:iqu6id$lsk$...
    >> 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?
    >>>

    >>

    >
    >
    >
     
    John Spencer, May 18, 2011
    #4
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Timothy

    Qry on Qry says Recordset not updateable

    Timothy, Sep 6, 2003, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    294
    John Viescas
    Sep 8, 2003
  2. lynn atkinson

    recordset is not updateable

    lynn atkinson, Apr 7, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    175
    gandalf
    Apr 7, 2004
  3. Guest

    Recordset is Not Updateable

    Guest, Apr 30, 2004, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    388
    Guest
    May 3, 2004
  4. Guest

    Recordset is not updateable

    Guest, Oct 7, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    155
    Michel Walsh
    Oct 8, 2004
  5. Guest

    Recordset not Updateable

    Guest, Mar 7, 2005, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    170
    Ken Snell [MVP]
    Mar 7, 2005
Loading...

Share This Page