Hi Pantera,
Yesterday night I tried the same thing, using the BEFORE CHANGE event, and it works. I don't know for what reason it is not working with AFTER UPDATE, like occurs in the Lynda video, but that is ok.
I'm not familiar with the video you mention, but I believe it could be incorrect depending upon how the information was presented. We actually made a late change to the product in this specific area of data macros during the Beta phases. It's very possible that Lynda was using an older Beta version of the product and the specific example will not work in the released Access 2010 product.
Back to your questions concerning this space. Here is some information which I hope will help you understand what is going on. Based on what you are trying to do, you really should be using the BeforeChange event instead of the AfterUpdate event for data macros. Why? Good question. Here's a good rule of thumb that will be easy to remember:
If you need to edit some data in the *same* record based on values in other fields in the same record, you really should use the BeforeChange event.
Essentially in your case you want to change the value of the Salary field based on what Department is entered. So is it possible to use AfterUpdate for this? Yes it is, but I don't recommend it for several reasons.
1. It is inefficient. If you use the AfterUpdate event, you have to commit your record changes (the Dept change) and then you're asking Access to go change the same record yet *again* and update the Salary field. That's two passes on the same record when one is more efficient. With BeforeChange, you can do this type of checking and editing all at once and make one record save instead of two.
2. You're way more likely to fall into the trap of data macro recursion - the data macro logic running over and over again. For example, if you do your scenario in the AfterUpdate event, here's what would happen if you weren't paying attention to your logic:
- You make a change to the Dept field. Record gets saved.
- AfterUpdate fires now. Access looks at Dept field and writes the value you want to Salary field. Record is saved again.
- AfterUpdate fires yet again. Why? Well you just had it change the Salary field remember? Salary field gets written again based on your logic and record is saved for a third time.
- AfterUpdate fires yet again and again and again....
- Access repeats this loop for 10 times and then bombs out (stops any more loops) because it will be a never ending cycle.
So in order to prevent the data macro recursion loop, you would have to use the "Updated" function to determine if the Dept field has changed so it doesn't keep running the same AfterUpdate event over and over again.
3. For your scenario, you would also have to write some duplicate logic in the AfterInsert or BeforeChange to do the same thing. Why? As written, if you just had this logic in the AfterUpdate event, your salary field won't get updated when you enter a new record.
One debugging tool you should really take advantage of with data macros is the USysApplicationLog table. Do you know how to use that?
When running your original tests, you should see the words "New Application Errors" displayed in the Status Bar. This means Access ran into one or more problems executing your logic. If you click those words in the Status Bar, Access opens this log table where you can examine the errors. You can also open this table from the Backstage View. Click the button labeled "View Application Log Table" in the Backstage view.
If you look at your original error, you would have seen this text:
"EditRecord failed because the default alias represents a record which is read only."
This was the late change we made to the product for the AfterUpdate event.
Utilize this log table when debugging and working with your data macros.
So as already mentioned, use the BeforeChange event as suggested by the other responders. In case you're curious, here is how you can make it work in the AfterUpdate, *but* I don't recommend it and it won't cover new records. See attached screenshot.
I hope that helps clarify things a bit for you.
--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation
Author -
Microsoft Access 2010 Inside Out
Co-author -
Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info:
http://www.AccessJunkie.com
----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------
