none
Access Web App: Create a YES value in table if all related table values are YES. RRS feed

  • Question

  • I have two tables PROJECTS and ORDERS.

    When you input a new ORDER you can add a project number to it, so they are related. A PROJECT usually has 0 to 10 different orders. When ORDER gets confirmed by supplier you can check checkbox on it CONFIRMED = Yes.

    I want to know if ALL input orders for a project are confirmed. Best would be to have a value in PROJECT table which is NO by default but after there are ORDERS related to it and ALL of them are confirmed the value changes to YES. So I can run on update macro and send an email telling that ALL of the PROJECT ORDERS are confirmed.

    I tried to make it with queries but Im not sure if its the correct route. Is datamacro better suitable for this. Or are there some other ways to achive same result?

    Tuesday, March 28, 2017 9:12 PM

All replies

  • I don't have an answer to your question, but just wanted to pass along some information regarding a new announcement from MS regarding AWAs, in case you weren't already aware, see: https://www.devhut.net/2017/03/27/steer-clear-of-access-web-applications-awa/ this way you have as much time as possible to plan what course of action you will take to migrate things.

    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Wednesday, March 29, 2017 12:49 AM
  • Wow, no I didnt know this, thank you for the info. I was so pleased with AWA, even if it didnt offer all of the access features. Implementing web based solutions with multiple users updating small data parts was probably never easier.

    What a letdown. 

    Wednesday, March 29, 2017 8:05 AM
  • Thank you for reply.

    The idea is not to send mail with an outlook but with AWA SendEmail function from the app. This can be achived with After Update macro which checks if selected field have been updated AND is TRUE. So my problem is mainly figuring out how to update one field to TRUE in PROJECTS table if all associated orders are CONFIRMED (value CONFIRMED ORDER = TRUE and all order for project are in this state).

    Wednesday, March 29, 2017 11:16 AM
  • Hello,

    We could create a data macro to for each records in table Orders to check if it is confirmed and return true if All is confirmed and then assign it to the AllConfirmed field of table Projects.

    E.g.

    The result:

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 31, 2017 6:58 AM
    Moderator