Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,326
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Non-updatable query (A2003)

    If I have a query that joins a table to itself, is it automatically non-updatable?

    Here is situation. I have a parent/child relationship between 2 tables. For each Parent, there are multiple records in the child table (one record for each Admin). The fields in the child table are (ParentID & AdminID comprise the PK):

    ParentID
    AdminID
    Notes

    On a form, I need to display side-by-side the notes for 2 admins for the same "Parent" record. I created a query joining the child table to itself based on the ParentID, and specifying the proper AdminID. The query works fine, and my form displays the info. Unfortunately, I can't update the fields, as the query is non-updatable.

    I can get around this by changing my form so it displays a record for the Notes from one Admin, then uses a subform to display the notes for the other one. But I don't understand why the original query is non-updatable.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Non-updatable query (A2003)

    There is no unique index on the link field (only), so the query is not updateable.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,326
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: Non-updatable query (A2003)

    I redid the query, starting with the Parent table and joining each occurance of the child table to it based on the ParentID field (unique to Parent table). Still not updatable. I then changed the recordset of the query to "Inconsistent Updates", and it allowed me to update it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •