Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2021
    Location
    Missouri Ozarks, USA
    Posts
    19

    Two similar edit queries - one works, the other does not

    I have an environmental database to manage aquatic invertebrate and river habitat data for long-term river studies. I am using parameter queries bound to forms for the purpose of editing the data. One query, called "qsel_edit_RiffleData" is used to edit riffle habitat data. The query, called "qsel_edit_DischargeDetail", is used to edit river discharge characteristics. They are so similar in design, yet the riffle query will allow edits while the discharge query does not. I have removed the parameters and inserted simple text criteria and the behavior of the queries does not change.




    I have pulled the two queries and the tables they reference along with some sample data into a container database called "MyTestContainer.accdb" which is attached. Is it possible to post screendump images of the query grid design views? That might help.


    Thanks in advance.


    c.r.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    some queries wont work depending on the joins.
    1 of your queries has 1 to many, the other is 1 to 1.

    you dont have to have the joined table to edit. If you need a field from the join (no longer there) use a dlookup on the form.
    removing the other table will allow edits.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In looking at your db, I see several things that I would call issues.

    In tables "tbl_RiffleData" and "tbl_DischargeDetail", it looks like they are linked in a 1-to-1 relationship. And they have a compound Primary Key. Then two field involved with the compound PK are text type fields.

    Looking at the SQL of the query "qsel_edit_DischargeDetail", there are two columns are for "Notes". While this does not hurt anything, I would delete one column.
    Code:
    SELECT tbl_DischargeDetail.Season, tbl_DischargeDetail.LocationID, tbl_DischargeDetail.EventID, tbl_DischargeDetail.DischargeNo, tbl_DischargeDetail.DistanceFromBank_m,
     tbl_DischargeDetail.Depth_cm, tbl_DischargeDetail.Notes, tbl_DischargeDetail.Notes AS Expr1, tbl_DischargeDetail.Units, tbl_DischargeDetail.DETimeStamp, tbl_Locations.ParkCode
    FROM tbl_Locations INNER JOIN tbl_DischargeDetail ON (tbl_Locations.LocationID = tbl_DischargeDetail.LocationID) AND (tbl_Locations.LocationID = tbl_DischargeDetail.LocationID)
    WHERE (((tbl_DischargeDetail.Season)=2007) AND ((tbl_Locations.ParkCode)="BUFF"))
    ORDER BY tbl_DischargeDetail.Season, tbl_DischargeDetail.LocationID, tbl_DischargeDetail.EventID, tbl_DischargeDetail.DischargeNo;
    Look at the FROM clause... don't know how you did this, but it doesn't look correct.
    If you delete the RED part , the query becomes editable (at least for me).




    Microsoft Access Tables: Primary Key Tips and Techniques

  4. #4
    Join Date
    Jan 2021
    Location
    Missouri Ozarks, USA
    Posts
    19
    In the original database, both queries had been 1 to many but I broke that loading it in the container database. DLookup() on the form control looks like a great alternative. So I will just yank the lookup table out of the query altogether.

    Thank you ranman and ssnafu for your responses!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. 2 similar queries acting different....
    By CQCDave in forum Queries
    Replies: 16
    Last Post: 04-29-2015, 01:05 PM
  2. Replies: 1
    Last Post: 07-29-2014, 06:00 AM
  3. Combine Similar Data From Three Queries
    By Kerberos in forum Reports
    Replies: 2
    Last Post: 05-02-2014, 11:27 AM
  4. similar code, one works, the other doesn't
    By johnseito in forum Access
    Replies: 15
    Last Post: 11-03-2013, 07:59 PM
  5. Replies: 4
    Last Post: 05-10-2012, 11:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums