Results 1 to 5 of 5
  1. #1
    Relwal is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    7

    Update Table using Continous Form that uses a Query

    I have the following query:


    Code:
    SELECT tbl_Clients.Client_Name, IIf(([tbl_Clients].[Active]=True),[tbl_NextMonthsPlan].[Days],Null) AS [Planned Days]
    FROM tbl_Clients LEFT JOIN tbl_NextMonthsPlan ON tbl_Clients.Client_ID = tbl_NextMonthsPlan.Client_ID
    WHERE (((tbl_NextMonthsPlan.Client_ID) Is Not Null) AND ((tbl_NextMonthsPlan.Payroll_ID)=[Forms]![frm_Weekly_Return]![txt_UserID]) AND ((tbl_NextMonthsPlan.Plan_Date)=[Forms]![frm_Weekly_Return]![txt_NextMonthsDate])) OR (((tbl_NextMonthsPlan.Client_ID) Is Null))
    GROUP BY tbl_Clients.Client_Name, IIf(([tbl_Clients].[Active]=True),[tbl_NextMonthsPlan].[Days],Null);
    I use this in a continuous form, but I am unable to update the records like this. How can I do this?
    This is the query result, so for example, I want to add in 'Planned Days' for other clients or change what is already there, so the 6 to a 4.
    Click image for larger version. 

Name:	screenshot3.png 
Views:	10 
Size:	13.9 KB 
ID:	26991

    thanks in advance.

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    It's the "grouped" query that's causing the issue I think.





    Sent from my iPhone using Tapatalk

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Temporary table?

    http://www.fmsinc.com/Microsoftacces...ble/index.html


    Sent from my iPhone using Tapatalk

  4. #4
    Relwal is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    7
    Quote Originally Posted by andy49 View Post
    Can I use a temporary table if the form / query will be accessed by multiple users at one time?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It's the "grouped" query that's causing the issue
    definitely, although not clear why you are grouping since you are not summing/averaging etc - and the left join will cause a problem as well - rule for updateable forms is one form one table.

    Suggest you make a continuous form (may be called a multiple items form in 2013) based on tbl_Clients only then have a subform in the footer based on tbl_NextMonthsPlan. Ensure the subform linkchild/master properties are completed - if you have set up your relationships as one to many, this will happen automatically.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-29-2015, 09:37 AM
  2. Populate list box with query on continous form
    By steve.earle in forum Forms
    Replies: 5
    Last Post: 08-13-2014, 08:38 AM
  3. Sort on Continous Form?
    By NotaryEtc in forum Forms
    Replies: 3
    Last Post: 04-19-2014, 08:35 AM
  4. Checkbox in continous form
    By senthilrg in forum Access
    Replies: 11
    Last Post: 12-05-2009, 08:49 AM
  5. Horitontal Continous Form?
    By Lawrence in forum Forms
    Replies: 1
    Last Post: 07-21-2009, 03:06 PM

Tags for this Thread

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