none
DataMacro AfterUpdate SetField not working no error thrown RRS feed

  • Question

  • I am building an Access App with SharePoint Online and Access 2013. 

    Here is my base relationships:

    Property -> Finance -> Units

    In that for a single Property there are one or more Finance sources and those Finance sources finance one or more groups of Units

    No problem for querying if you want to see all the Units under a Property, you just query it through Finance

    However, on the Properties Form, I would like to show tabs in the Related Items List for both Finance and Units. 

    So even though when I add a Unit record it is attached to the Finance record, I want to "tag" it with a PropertyID so that it would have both relationships.  If I was able to accomplish that, I could have both Finance and Unit tabs on the Property Form

    So on the Unit Table, which already has FinanceID Lookup, I added a PropertyID Lookup.

    As I understand it, it is not possible to have both FinanceID and PropertyID associated automatically to the Units table, because if I try that I get referential integrity errors.

    So what I wanted to do was to fill in PropertyID with an After Update DataMacro which will lookup the Finance Record and then bring in the PropertyID

    I have attached an image of the AfterUpdate DataMacro.  When looking at the Trace Table for the DataMacro, everything is executing without error, and it is finding and applying the PropertyID in the SetField (the image also contains a screen capture of the trace table)

    And even though all of this appears to work without error, the value of PropertyID in the Unit table is not updated.

    Is there something I am missing?  Why is this executing without error, yet not providing the intended results?

    I realize I can live without this working.   It would be really helpful, for user understanding, if they could see the Units when they view the Property Record, without having to open up the Finance record.

    I have already tried to just make the PropertyID field in the Units table just a number field, to avoid any potential problems with setting a value of a Lookup, but the results are the same.  No data is updated.

    Any help would be greatly appreciated.

    Thanks


    DWM

    Thursday, August 20, 2015 6:16 PM

Answers

  • Have you tried recursive triggers? Since SQL Server allows up to 32 levels of recursion, you could theoretically fire a event in a data macro that also tests whether the recursion would cause an infinite loop.

    Your OnUpdate trigger should look like this: test a boolean flag that detects whether the trigger has been called in a recursion and update your FinanceID field. As long as the trigger doesn't fire more than 32 times, this should be enough to prevent an infinite loop.

    See Chapter 7 of Andrew Couch's excellent "Extend Microsoft Access Applications to the Cloud".

    https://books.google.com/books?id=H0AFCAAAQBAJ

    Wednesday, August 26, 2015 5:55 PM

All replies

  • Although I don't know exactly what is going wrong with your data macro, I think your overall approach is wrong.  Rather than add a redundant PropertyID field to your Units table, make use of a query that joins the Finance table to the Units table and returns the PropertyID from the Finance table along with the UnitID and whatever fields you may want to include from the Units table.  You could base your RelatedItems subform on a union query that combines that query of the Finance & Units tables with a simple query of the Finance table.  It might look something like this:

    SELECT 
        PropertyID, 
        FinanceID As RelatedID, 
        "Finance" As ItemType, 
        FinanceDescription As ItemDescription 
    FROM Finance
    UNION ALL
    SELECT 
        Finance.PropertyID, 
        UnitID As RelatedID, 
        "Unit" As ItemType, 
        UnitDescription As ItemDescription
    FROM
        Units
    INNER JOIN
        FINANCE
    ON Units.FinanceID = Finance.FinanceID
    ORDER BY
        PropertyID, 
        RelatedID, 
        ItemType, 
        ItemDescription
        
    

    That was "air SQL", but I hope you get the idea.

    Using a union query like that relieves you of the need to denormalize your Units table by adding a PropertyID field to it.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 20, 2015 7:48 PM
  • I have already tried that.  I have written a query that joins Properties - Finance - Unit

    The problem is , apparently, that when adding a tab on the Related Items control, the only objects listed as available to be added as a tab are tables that are related to whatever main form you are working on.  So when I go to add a tab to Related Items I only see property_contacts and property_finance because those are two tables where PropertyID is a relationship


    DWM


    • Edited by Devin McMahon Thursday, August 20, 2015 8:20 PM add pic
    Thursday, August 20, 2015 8:18 PM
  • I'm sorry, while thinking about how it "ought" to be done, I'd forgotten that this is in a web app, where the options are more limited than in the desktop client.  I have no practical experience with Access web apps, so I probably shouldn't have stuck my oar in.  But can you create a Related Items control, selecting one of the options it gives you, and then edit the RecordSource property of that control to use a SQL query like the one I posted, or else save that query as a view and set the control's RecordSource to that view?

    I'm not sure if that's possible, or how well it would work, because I can't try it out myself nor look for workarounds, and I'm sure there's built-in functionality in the RIC that may have a problem with a union query as a recordsource.  But it's an idea.

    If that doesn't work, I guess we're back to your original approach of adding a redundant PropertyID to the Units table, and we'll have to go back to debugging your data macro.  :-/   What event are you using to execute that macro?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 20, 2015 8:38 PM
  • Thank you for your quick response..

    I am using AfterUpdate and as you can see from the Trace Table, it is executing without error, and it is getting the correct value for PropertyID

    I read somewhere that this kind of thing should be handled with BeforeUpdate, but Before Update is not available in the Access App, so again, this may be a problem with a limitation of an Access App.

    I was considering executing a named DataMacro from the form and passing a parameter if this will not work.

    Thanks again


    DWM

    Thursday, August 20, 2015 8:42 PM
  • I'm just using my imagination here, because I don't have any AWAs to try things out on.  But I wonder if, in your data macro, you need to specify that the PropertyID field to be updated is in the property_unit table.  In other words, the macro action would say:

        SetField
            Name    property_unit.PropertyID
            Value    = [property_finance].[PropertyID]


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 20, 2015 9:04 PM
  • When trying to enter the fully qualified name property_unit.PropertyID to the Name field, the Macro fails compilation with an "macro AXL is not valid" error

    I am beginning to think that this is just not supposed to work in an Access App


    DWM

    Thursday, August 20, 2015 9:12 PM
  • When trying to enter the fully qualified name property_unit.PropertyID to the Name field, the Macro fails compilation with an "macro AXL is not valid" error

    I am beginning to think that this is just not supposed to work in an Access App

    I'm sure you can accomplish your goal; you just need advice from someone with experience in Access Web Apps. 

    I can see how there could be a problem updating a record in a table in its own AfterUpdate event, since that sort of thing can lead to an infinite loop.  In an Access client database, you would absolutely have to use a form's BeforeUpdate event, not its AfterUpdate event.  But I don't know enough about how events and triggers work in AWAs to say what you can and can't do in this environment.  I'll do some research on that.

    In the mean time, how about this?  If you are currently trying to run your macro in the view's AfterUpdate (OnUpdate?) event, how about using the AfterUpdate event of the control that sets the FinanceID for a unit?  That shouldn't have any problems with loops or lockouts, since the record isn't in the process of being saved yet.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 20, 2015 9:22 PM
  • It occurs to me that you may need to capture the PropertyID to a local variable in your LookupRecord block, then end that block, and *outside of that block* set the field in the property_unit table to the value of the local variable.  Otherwise, Access may think you want to edit the record in property_finance that you just looked up.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 20, 2015 9:31 PM
  • I have successfully created a Named DataMacro that works.  The only issue I have now is where to fire it.  It works OnCurrent of the Units form, but that means that someone has to browse the records in the Units screen to make it fire, and it will not work when entering a new record.   Anyway, its a start and I may not end up using it, because this is only to allow viewing of Units in the Related Items Control.


    DWM

    Friday, August 21, 2015 7:15 PM
  • Although I still don't have an Sharepoint site to test things out, I've been doing some reading, and it seems to me that you should be doing this in the BeforeChange event of the [property_unit] table.  Did you try your original data macro in that event?  It seems to me that the logic of the macro in your first post should work in the table's BeforeChange event.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, August 24, 2015 12:47 AM
  • The BeforeChange and BeforeDelete events are not available in an AccessApp Database.  When designing a table that is a created for an online database, the only events available are On Insert, On Update and On Delete.  There are not even any "After" events listed.

    I would imagine that this is because the desktop database is using something like Jet, while the Online App database is using Azure SQL, which changes the nature of the events.

    So it looks like the bottom line is that the lack of "Before" events is just a limitation for the Online App Database.

    If you have an example of an article or forum where they talk specifically about the BeforeChange event in the context of an Access App, please post it here as I have been unable to find one up to this point.

    Thanks


    DWM

    Monday, August 24, 2015 1:18 PM
  • The BeforeChange and BeforeDelete events are not available in an AccessApp Database.  When designing a table that is a created for an online database, the only events available are On Insert, On Update and On Delete.  There are not even any "After" events listed.

    I would imagine that this is because the desktop database is using something like Jet, while the Online App database is using Azure SQL, which changes the nature of the events.

    So it looks like the bottom line is that the lack of "Before" events is just a limitation for the Online App Database.

    If you have an example of an article or forum where they talk specifically about the BeforeChange event in the context of an Access App, please post it here as I have been unable to find one up to this point.

    It's possible the discussions I was reading were talking about the Sharepoint-based web apps supported by Access 2010 -- I'm not sure.  The On Insert, On Update, and On Delete events you describe sound like good old-fashioned triggers, so I'm going to guess that's what they are.  Have you tried using the On Update event for your macro?  You may need to first test with an If statement to see if the PropertyID field in the [property_unit] table is Null, and only update it if it isn't, or else only update it if it has a value different from what you would be setting it to.  I don't know whether that's necessary or not, but I could see it as a way to avoid an infinite loop.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, August 24, 2015 5:00 PM
  • Hi Devin,

    >> The BeforeChange and BeforeDelete events are not available in an AccessApp Database.  When designing a table that is a created for an online database, the only events available are On Insert, On Update and On Delete.  There are not even any "After" events listed.

    Since your available event is On insert, I assume that your Access version is Access 2013. If so, you are right, the BeforeChange and BeforeDelete are not available in Access 2013 web app, they are available in Access 2013 desktop database.

    >> It works OnCurrent of the Units form, but that means that someone has to browse the records in the Units screen to make it fire, and it will not work when entering a new record.

    For your current requirement, the On Insert would work. When you add On insert macro to a table, it will fire when new record is added to the table. If you want the event be fire when you modify the data in the table, you could use On Update.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, August 25, 2015 5:49 AM
  • OnUpdate, which is when I need the event to fire because that is where a FinanceID is assigned, the macro will not fire because it would cause an infinite loop

    I am afraid this is just not attainable in Access App, unless it is fired manually


    DWM

    • Proposed as answer by tonmcg Wednesday, August 26, 2015 4:38 PM
    • Unproposed as answer by tonmcg Wednesday, August 26, 2015 4:38 PM
    Wednesday, August 26, 2015 2:52 PM
  • OnUpdate, which is when I need the event to fire because that is where a FinanceID is assigned, the macro will not fire because it would cause an infinite loop

    Does that happen even if you enclose the macro actions that update the PropertyID in an If block, so that they only execute if the value is currently Null?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, August 26, 2015 4:43 PM
  • Have you tried recursive triggers? Since SQL Server allows up to 32 levels of recursion, you could theoretically fire a event in a data macro that also tests whether the recursion would cause an infinite loop.

    Your OnUpdate trigger should look like this: test a boolean flag that detects whether the trigger has been called in a recursion and update your FinanceID field. As long as the trigger doesn't fire more than 32 times, this should be enough to prevent an infinite loop.

    See Chapter 7 of Andrew Couch's excellent "Extend Microsoft Access Applications to the Cloud".

    https://books.google.com/books?id=H0AFCAAAQBAJ

    Wednesday, August 26, 2015 5:55 PM