You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Access aggregate queries provide a popular and powerful way to keep track of totals and summarize all the data in a table. Data Macros introduce a new way to keep track of these types of totals traditionally done in aggregate queries or populated in reports. Using the calc and store model, you can store the de-normalized total in a field on their table and update it with After Event data macros every time a related record is inserted, updated or deleted. Depending on the needs of the database, it may be more efficient to calculate the totals when the data is entered using data macros rather than every time the data is queried. As well, since aggregate queries are not supported in Web applications, it’s a great alternative to keep track of these totals. Let’s step through how to set up this logic.
In the Charitable Contributions template, we have a TotalDonated field on the Donors table that lists the amount of money the donor has donated. This value is maintained using a named data macro that is called from the After Insert, After Update and After Delete events of the Donations table. The named data macro is below and looks up the first record in the Donor table where the Donors.ID field is the same as the Donations.DonorID field. It sets the Donors.TotalDonated field to the Donors.TotalDonated + the Amount from the parameter prmAmount.
This generic code allows us to reuse it for each After event. In the case a new Donation has been added, we just call the named data macro from the After Insert event using RunDataMacro and pass in the Donor who made the donation and the Amount of the donation as parameters.
We can call the macro from the After Update event to ensure the donations are up to date when the Donor giving the donation has been updated or has decided to change the amount. In this case, we just check to see if the Donations.DonorID field has been updated and remove the donation from the previous Donor and add the amount to the new donor so the total is up to date. In the case that the amount of the Donation has been changed and the Donor has not been changed, we keep a LocalVar of the changed amount and then add it to the Donors.TotalDonated field.
We can also call the data macro when a donation has been deleted on the After Delete event. Similar to the After Insert event, we call the named data macro using RunDataMacro and pass in the Donor who made the donation and subtract the Amount of the donation in the parameters.
We also have a named data macro that can be called to recalculate the donations in case you add the TotalDonated field after donations have already been entered. The logic is as follows and for each record in the Donors table iterates over the Donations table and updates the value in the TotalDonated field.
The code reuse by calling one named data macro from all the events allows for easy readability and ensures that any modifications made to the macro in the future is inherited by all the events. You can download the Charitable Contributions template to get this logic and incorporate it in your apps.
Can you please explain the use if the 'Alias' in the examples?
Not directly related to this post, but it is related to Data Macros..... what is SetReturnVar and how is it used, please? Alan
ReturnVars are something new that we added in the RC build. It allows you to return values from the data macro to the caller. IMO - super powerful and useful stuff. I have a post in the works on the topic.
ReturnVars sound intriguing. I'm looking forward to hearing how you use them and where you can return the data to.
>Can you please explain the use if the 'Alias' in the examples? Just think of an Alias like opening a recordset. If I need more then one table opended, then how do I distinguish in code that I want to use Company column from table1, or table2 if they BOTH have the same column name? So, in VBA code, I open table1 to something called rstTable1, and for table 2, I open it to recordset called rstTable2. So, an Alias is just a way to give a name to the table your using in code, and thus distinguish between those tables. If your code was only ever to open one table, then this would not be a problem. When you open several tables at once then Alias is just a quick way to keep them separate in code. In fact, you might even have code that opens the same table more the once (with a different filter). So we can give each table we open a Alias name. In many macro examples, you see the Alias is left blank, and that’s ok when you not worried about what columns your referencing in code, but when you have more then one table opened, using the Alias is a good idea and helps you distingiush what table collums you are working with.