Software-Hotbuy www.software-hotbuy.com

Access 2010: data macros to create aggregates

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. 

Office 2007 professional and Office 2007 Ultimate are so powerfull.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.

After Insert event

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. QuickBooks 2010 is so Helpful!

After Update event

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.

Photoshop CS4 is so magic!  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.Acrobat 9 is so useful!

After Delete event

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. Dreamweaver CS4 is very easy-to-use!

Named macro

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.

Enjoy!

Data Macro Aliases and the Where Condition Demystified

Whenever I debug a data macro, one of the most common mistakes I find is misusing aliases, especially in the Where Condition of a ForEachRecord or LookupRecord data block.  This blog post is an attempt to demystify this area.  Hopefully, by the time you are done reading this you will have a firm grasp of how to use aliases in data macros.

Key points

1. What is the default data context?

2. When does the default data context change?

3. What else is in the data context?

4. What is the default data context in a named data macro?

5. Using an alias in the Where Condition of a ForEachRecord or LookupRecord.

6. Using an alias to reference a data context outside of the current default data context.

What is the default data context?

In a data macro, the "default data context" is what you are using when you refer to a field without using a table name qualifier.  

Office 2007 professional and Office 2007 Ultimate are so powerfull.When a BeforeChange, BeforeDelete, AfterInsert, AfterUpdate, or AfterDelete event data macro is triggered, a new default data context is created, which can be used inside that block.

This new context is what I like to refer to as the “incoming default data context” or, for brevity, the “incoming record.”  Note that in event data macros, the incoming default data context is always read-only.  This is a change in behavior from the Beta 2 release. Windows 7 is also my love!

When does the default data context change?

The data context stack is expanded and the default data context changes whenever you enter a data block (ForEachRecord, LookupRecord, EditRecord, and CreateRecord).

For example, if you are in a data macro in Table1, and you call “ForEachRecord Table2”, and then refer to a field without a table name qualifier, you are referring to a field in Table2. QuickBooks 2010 is so Helpful!

What else is in the data context?

If you are in Table1.AfterUpdate, then you call “ForEachRecord Table2”, and inside that loop you call “LookupRecord Table3”, the data context at this innermost point contains a record for each of the 3 tables, and can be accessed using the following table name qualifiers:

1. [Table3] contains the record (if any) found by LookupRecord.

2. [Table2] contains the current records in the ForEachRecord loop.

3. [Table1] contains the record that triggered the AfterUpdate event.

Acrobat 9 is so useful!

4. [Old] contains the record in Table1 from just before the data change that triggered the AfterUpdate event was entered.

At the innermost data block (the LookupRecord on Table3), all of these are available from the data context stack with [Table3] being the current default data context.  So, inside the LookupRecord data block, the data context stack looks like this:

When you exit a data block, the data context for that block is removed from the data context stack.  So, if the LookupRecord on Table3 is finished, but you are still inside the ForEachRecord on Table2, then [Table2] becomes the current default data context, and the data context stack looks like this:

What is the default data context in a named data macro?

If you call a named data macro from an event data macro, the entire data context is carried over.  

Dreamweaver CS4 is very easy-to-use! So, in the above example, if you called RunDataMacro from the innermost data block, all 4 items would still be available inside the named data macro.

Note that there is no data context if you call a named data macro from a user interface macro (UI macro).  In this case, any attempts to refer to a field will fail until you enter a data block, because there is no way to know which record you are referring to.

TIP:

If you define parameters for data that is consumed by your named data macro, then you can also call the named data macro from a UI macro.  Then, if you are viewing a form you can pass the ID of the record being viewed (or any other field in it) to the data macros and then use LookupRecord on the ID to get the same record and perform some processing, such as deleting the record. Note however, that parameter values in data macros are read-only and cannot be edited.

Here is a screenshot illustrating this:

Using an alias in the Where Condition of a ForEachRecord or LookupRecord.

In most cases, you will not need to define an alias in a ForEachRecord or LookupRecord and you can just leave it blank, because the alias defaults to the same name as the table or query you are referring to on the first line.

IMPORTANT: The Where Condition is inside the data block.  Therefore, the default data context in the Where Condition argument is whatever table or query the ForEachRecord or LookupRecord refers to.

Suppose you are in Table1’s BeforeChange event, and you want to prevent the change if it would create a duplicate value in the [Text1] field.  The easiest way to do it is to create a LookupRecord on Table1 to find out if there is another [Text1] field in the table with the same data as the incoming record, and raise an error if a duplicate is found.

The solution is to define an alias for the LookupRecord block, then use that alias as the table name qualifier for fields in the Where Condition expression, and call the RaiseError action if any record is returned by LookupRecord to prevent the entry of this data.

Here is a screen shot illustrating this:

Using an alias to reference a data context outside of the current default data context.

Let’s say you want to iterate over all the records in Table1 and look for a match in Table2.Text1, and if found, set Table1.Text2 = "Match found in Table2 at row ID=X".  To do this, I’ll define an alias at each step.  Then, to edit the record from the ForEachRecord instead of the LookupRecord (which would be the default data context for the EditRecord if no alias was specified), I’ll use the alias I defined for the ForEachRecord (T1) in the Alias argument of the EditRecord block.  This is how to edit the record from the ForEachRecord loop and instead of the record found by the LookupRecord.

Here is a screen shot illustrating this:

TIP:

The other benefit of always defining your aliases and then using them in the expressions inside the data block is that if you rename your table, you do not need to update all of your expressions.

That’s it!  I hope this clears up any questions you may have had about using aliases or the where condition in data macros.

Give us feedback – How can we help you work faster and more efficient?

Today’s guest writer is Neha Monga, Program Manager on the Access team. She works on compatibility checker, the runtime, Access developer extensions, and the future of the Access user experience.  

Ms office 2007 are so Charismatic.I’m starting to think about ways to improve the Access user experience to make YOU faster, more efficient and smoothly connect to what comes before and after.  

Office 2007 professional and Office 2007 Ultimate are so powerfull.I would love to get feedback from you on the following areas:

What tasks do  you do often, and you wish Access were faster or more efficient?  QuickBooks 2010 is so Helpful!

Example – if you were to use the date picker to change the date to many years in the past (such as a birthday), it will take a lot of clicks to go back each year. It would be nice to be able to ‘jump’ to a specific year.

1. Are there any scenarios where you find Access is not responsive and the operation takes too long?

Example – a query that ran faster in a previous version of Access. Acrobat 9 is so useful!

1. Are there scenarios where the sequence of operations is not intuitive? i.e. you don’t understand what to do next (until you read about another Access ‘gotcha’)?  Dreamweaver CS4 is very easy-to-use!

Example – how to create re-occurring Outlook saved export task.

1. Are you able to easily start off, pause, stop and pick up from where you left or do you lose work when you attempt to do that?

Example – you work on an object and close the database but the navigation pane doesn’t reselect the object and you need to go find it.

I look forward to hearing about your scenarios, steps, pain-points and fast and fluid user experience suggestions! No promises but I have a high hopes your feedback will have a big impact in future releases. You can post responses here or send email through the blog. 

Thanks in advance!

Power Tip: Distinguish between an unsaved record and a saved one

When you are in the process of entering a new record in an Access database, you can press the Escape key to cancel the entry of the record. However, some events, such as clicking or tabbing into a subform on the main form, can save the record on the main form. 

Ms office 2007 are so Charismatic.At that point, you can't press Escape to cancel the entry—you must use the Delete command to delete it. The end-users of your database might not understand the difference, and would probably want a single button or keyboard shortcut that just gets rid of the record, regardless of whether it’s been saved or not. To do this, you need to be able to which of these two states the record is in:a) The record is being built up, has been assigned an AutoNumber ID, but hasn't been committed to the table yet
- OR -
b) The record has already been committed and the ID indicated is already persistent.

The solution is to add a module-level variable (mboolIsInsert ) to the parent form. The variable is set to True in the BeforeInsert event on the form, and reset to False on the OnCurrent event. The OnCurrent event doesn’t fire for the parent form when you click or tab into the subform, only when you directly navigate on the main form.  You can then check the value of the module-level variable in your code to see if you’re in an insert event and respond as appropriate. Office 2007 professional and Office 2007 Ultimate are so powerfull.

Option Compare Database
Option Explicit

Private mboolIsInsert As Boolean

Private Sub Form_BeforeInsert(Cancel As Integer)
    mboolIsInsert = True
End Sub

Private Sub Form_Current()
    mboolIsInsert = False
End Sub

Public Sub CancelOrDeleteRecord()
   If mboolIsInsert Then
        'Add code to perform escape-equivalent cancel
   Else
        'Add Delete command to delete the current record
   End If
End Sub

Running the CancelOrDeleteRecord() procedure (for example, from a button on the main form) provides a more usable form by always getting rid of the record, regardless of whether it's been saved or not. QuickBooks 2010 is so Helpful!Thanks to Access PM Russell Sinclair for the tip!

Office 2007 Released to Manufacturing

I'm proud to announce that last Friday, November 3 at approximately 2:30 PM, we signed off on build 4518.1014 as the 2007 Microsoft Office system and released it to manufacturing.

This was followed by a ship party for everyone in Office including a few minutes of speeches, the traditional sounding of the RTM siren, and plenty of champagne (much of which I ended up wearing.) The rain even stopped for a few hours!

You can read the official press release here. 

This release has been such a great pleasure to work on. It was more than three years ago when Julie walked into my office (I barely knew her at the time) and asked if I wanted to come over to the user experience team to help figure out if there was something better than menus and toolbars. I had no idea what an adventure I was about to embark on!

Three years later and we've shipped a product that I'm so proud of.

In the near future, we'll be sharing more of the research we've been doing on Beta 2 and B2TR deployments around the world. Just last week, we got back the most recent round of data from ~4000 people in long-term enterprise deployments of Beta 2 in the United States, Europe, and Asia. The results are positive beyond what I would have dared to dream.

To those of you who have been active in the beta program and here on the blog, and to everyone who sent feedback on the betas, thank you. Your feedback truly made the product better than it ever could have been without you.

To the app teams and partner teams around Office and at Microsoft who helped make this release possible, thank you. This couldn't have happened without your ideas and support.

And to those of you on the UEX team, whose ingenuity, consummate engineering, missed weekends, and tireless attention to detail made it all possible—thank you! We did it!

How to get it?

Office 2007 products will be available at retail early in 2007, so you'll be able to get your hands on them soon. Business customers will be able to get Office 2007 through the volume licensing program before the end of the year.

If you're running the Beta 2 Technical Refresh, the good news is that you can keep using that build for quite some time: the client build expires on March 31, 2007, and the server products expire on May 15, 2007.

What's Next?

Between now and retail availability, I'm going to do a series of posts documenting the creation of the Office 2007 user interface from the earliest prototypes all the way to the final product.