locked
Append Data Macro in Web App RRS feed

  • Question

  • I have limited experience with Data Macro's but I need to Append a Child table when the Parent adds a new record.

    Three tables: Classes, Sessions, Attendees

    Scenario: "Class A" has a one "Session" with "25 Attendees"

    If another (or more) Session is added to Class A I would like to be able to ADD the same 25 Attendees to the new Session without selecting each individually. Can this be done with a Data Macro?

    Thank you in advance.


    John Fletcher

    Saturday, May 9, 2015 2:50 AM

Answers

  • ​Hi John,

    >> If another (or more) Session is added to Class A I would like to be able to ADD the same 25 Attendees to the new Session without selecting each individually. Can this be done with a Data Macro?

    You could achieve this by using event data macros. Data macros come in two flavors: event data macros and standalone macros. You could select the table “Classes”, click the “On Insert” to add the event data macro, use ForEachRecord Data Block to get the 25 attendees, and in the ForEachRecord Data Block use CreateRecord Data Block to create a new record in the specified table.

    # ForEachRecord Data Block
    https://msdn.microsoft.com/EN-US/library/office/jj249636.aspx
    #CreateRecord Data Block
    https://msdn.microsoft.com/en-us/library/office/jj249720.aspx

    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.

    Monday, May 11, 2015 8:22 AM

All replies

  • ​Hi John,

    >> If another (or more) Session is added to Class A I would like to be able to ADD the same 25 Attendees to the new Session without selecting each individually. Can this be done with a Data Macro?

    You could achieve this by using event data macros. Data macros come in two flavors: event data macros and standalone macros. You could select the table “Classes”, click the “On Insert” to add the event data macro, use ForEachRecord Data Block to get the 25 attendees, and in the ForEachRecord Data Block use CreateRecord Data Block to create a new record in the specified table.

    # ForEachRecord Data Block
    https://msdn.microsoft.com/EN-US/library/office/jj249636.aspx
    #CreateRecord Data Block
    https://msdn.microsoft.com/en-us/library/office/jj249720.aspx

    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.

    Monday, May 11, 2015 8:22 AM
  • Hi Edward,

    Seems awfully confusing. I was never able to get it to work. This is the query I run in access client that I am trying to replicate in the Web App.


    John Fletcher

    Monday, May 18, 2015 4:38 PM
  • Hi SpaceData,

    >> This is the query I run in access client that I am trying to replicate in the Web App.

    Yes, you are right. There is no action query in access web app, and you need to use Data Macro in the Access web app. As the reply above, you could use event data macros to achieve appending records when the parent table add a new record.

    If it did not work, could you share us what you have tried and the issue you have.

    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, May 19, 2015 9:36 AM
  • John,

    I was wondering something similar. I wanted a way to append an existing table with additional records although I was just trying to do it from a new temporary table with the same fields as the existing one. A little different purpose but my method should work for you.

    1. Create a query that returns just the records you want to add to the new table.
    2. Create a data macro that uses the following logic:
    • Start with ForEachRecord action on your query; no Where condition
    • Use the SetReturnVar action and set a variable for each field you want to use when you append your table; my example used three
    • Then use a CreateRecord action on your target table
    • Use a SetField action for each field you set up a previous SetReturnVar variable for

    My logic looks like this:

    Data Macro Example


    I just discovered that I didn't need to use the variables.  I was able to write the query fields directly to the table.

    Data Macro Direct

    Gary Michalske



    • Edited by Gary Michalske Monday, October 12, 2015 11:09 PM Added another image
    Monday, October 12, 2015 10:08 PM
  • Two suggestions:

    Change your data model in which you add sessions to a Class. That way you add attendees to the class. Thus when you add or remove sessions, only a single session record need be added to the class (and thus no need to attach sessions too attendees).

    The above would thus eliminate the need to copy the records.

    I should also point out that if you are using web 2010, then you cannot use the create record command inside of a for each record loop.

    You can use create record inside a for/each/record loop in web 2013, and thus the second suggestion would be to use a for/each/record loop with a create record inside as others suggested here.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Tuesday, October 13, 2015 1:52 AM
  • Hi Albert,

    Thanks for the reply. My model may already be like you suggested. [Classes] have both [Session] and [Participants]. A fourth table [Attendance] contains both a [SessionID] and a [ParticipantID]. In desktop Access it was easy to append the Session and Participant to Attendance when a new Session was created. Not sure what to do with Data Macro. How would the For/Each/Record loop work in this scenario.

    In other words:Add Session


    John Fletcher

    Wednesday, October 28, 2015 2:51 PM
  • It timed out on me sorry...

    to continue: On new Session create new Roster with SessionID and all Class/Participants for the Class/Session.

    Hope this makes some sense.

    Thank you,

    John


    John Fletcher

    Wednesday, October 28, 2015 2:55 PM
  • Hi Gary,

    I tried your suggestion and it works. However, I need it to only append to the current form [SessionID]. That is, I placed a button on the Session List form to "Add Participants" but it adds to all Session dates not just the one I want. In Access desktop this was handled in the query criteria by "Forms!Sessions!SessionID" but I don't know how to replicate this in the Web App.

    John


    John Fletcher

    Monday, November 2, 2015 9:06 PM
  • Hi John,

    Sorry for taking so long to respond. I've been out of town and very busy with work.  Have you figured this one out since your last question?


    Gary Michalske

    Monday, November 23, 2015 1:23 PM
  • Hi Gary,

    No I have not been able to restrain the "Create Record" to current SessionID. Seems like I need a "Where" clause or Parameter bound to the "Session List" form but this is not possible in Access Web. Currently the data macro creates records for all Sessions and I need it to only Append the current Session.

    Thanks,

    John


    John Fletcher

    Monday, November 23, 2015 4:18 PM
  • Well there is a Where parameter in the For Each Record action and in the Look Up A Record action. The Where parameter has a Expression Builder associated with it so maybe you could try that to limit for the [Session].[ID].


    Gary Michalske

    Monday, November 23, 2015 4:44 PM