Results 1 to 5 of 5
  1. #1
    MTM is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    20

    Cannot edit inner query

    I have two, what look to be identical tables and queries, yet one allows me to edit the query and one does note. The queries have two inner joins as shown below:

    SQL for the query I can edit:


    SELECT BaseCoat_Front_shorter.ID_Lot, BaseCoat_Front_shorter.Product, BaseCoat_Front_shorter.Owner, BaseCoat_Front_shorter.Priority, BaseCoat_Front_shorter.Meters_In, BaseCoat_Front_shorter.[Tool_#], Eng_BC_Param_BE_shorter.[Base Thickness]
    FROM BaseCoat_Front_shorter INNER JOIN Eng_BC_Param_BE_shorter ON (BaseCoat_Front_shorter.Product = Eng_BC_Param_BE_shorter.Product) AND (BaseCoat_Front_shorter.[Tool_#] = Eng_BC_Param_BE_shorter.[Tool_#]);

    SQL for the query I can not edit fields:
    SELECT BaseCoat_Front_New.ID_Lot, BaseCoat_Front_New.Product, BaseCoat_Front_New.Owner, BaseCoat_Front_New.Priority, BaseCoat_Front_New.Meters_In, BaseCoat_Front_New.[Tool_#], Eng_BC_Param_BE_New.[Base Thickness]
    FROM BaseCoat_Front_New INNER JOIN Eng_BC_Param_BE_New ON (BaseCoat_Front_New.Product = Eng_BC_Param_BE_New.Product) AND (BaseCoat_Front_New.[Tool_#] = Eng_BC_Param_BE_New.[Tool_#]);

    When I try to edit the query data fields, Access give me a beep, but no message.

    From my examination of the SQL's I do not see a problem. I am thinking there is something about one of the tables that is not allowing me to edit the data. I can change the data in each individual table, just not in the query.

    Any ideas?

    Thank you for any help. Mike

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    queries become uneditable for a number of reasons - use of DISTINCT or GROUP BY for example. They can also become uneditable if there are too many tables in the query and/or relationships are not defined in a way that access can determine which record for which table is being edited. I would check your relationships to make sure you have proper primary/foreign keys defined.

    Generally speaking you should not be editing queries - use a form and and subform (rule is one table, one form)

    Sometimes you can make a query editable by changing it's properties from dynaset to dynaset inconsistent updates

    Side note: using # in a field name can cause inexplicable errors, even when using square brackets, field names should only contain alphanumeric characters.

  3. #3
    MTM is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    20
    Thank you Ajax... setting Record Type to dynaset inconsistent updates does indeed work.

    My situation is that in addition to the data the user needs to fill in on the form, the user needs information from another table to decide what data to enter and hence the query that combines two tables (and is the table record source). In this case I do not see how to use the rule "one form one table".

    Thank you for your help! Mike

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    setting Record Type to dynaset inconsistent updates does indeed work
    it won't for all situations - better to avoid them in the first place

    the user needs information from another table to decide what data to enter and hence the query that combines two tables (and is the table record source). In this case I do not see how to use the rule "one form one table".
    use a subform, or dlookups

  5. #5
    MTM is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    20
    Thank you, I will consider your suggestions. For now, I consider my immediate problem solved... it may be return...

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

Similar Threads

  1. Can't edit a Group by Query
    By Canadiangal in forum Queries
    Replies: 2
    Last Post: 06-08-2013, 02:12 PM
  2. Replies: 1
    Last Post: 05-31-2013, 08:53 AM
  3. Replies: 1
    Last Post: 09-06-2012, 01:40 PM
  4. Unable to edit Data in Query
    By undee69 in forum Forms
    Replies: 4
    Last Post: 06-10-2012, 10:43 PM
  5. Edit a table in a query by renaming the query?
    By TheWolfster in forum Queries
    Replies: 2
    Last Post: 07-30-2010, 02:57 PM

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