Viewers: in countries Watching now:
In Access 2010 Power Shortcuts, Access expert Alicia Katz Pollock shares hundreds of tips and shortcuts to vastly increase efficiency and get the full power out of Access 2010. The course includes tips for working with the Ribbon and Quick Access toolbar, managing files, customizing and automating Access, rapid data entry and editing, working with tables, queries, forms, and reports, managing your database, and much more. Exercise files accompany the course.
A Before Change Data Macro allows you to perform an action upon adding, updating, or deleting a record. For example, it's a good idea to have date and time modified fields in your database, so that you know when changes were made to your significant data. For example, open up the Products table. Here is a list of all the products we sell along with their prices and costs. If we update either our retail Price or our wholesale Cost, it would be very useful to have a historic record of the last time any change was made to each product.
To do that, let's go into the Design View, and add the fields we need. I'll right-click on the Products tab, and choose Design View off the shortcut menu. We'll start by adding our date modified field. I'll type in DateModified. Tab to the Data Type, type in a D, which will auto fill Date and Time. Then I'm going to come down to the Format row down in the Field Properties, and change that to Short Date. The next field that I'll create is called TimeModified. It will also be a Date and Time Data Type, and the Format will be Short Time, which will give me military time for the change that I'm making.
Now we're going to add our Before Change Data Macro to our table. A Data Macro is specifically tied to the table itself. So while we are in table Design View, there is a Create Data Macros button and I can choose Before Change. There is another place where I could do this as well. I'm going to switch my view back to the Datasheet View. When it asked if I want to save the table, I'll definitely say Yes. When I go up to the Table tools Ribbon and choose the Table tab, there is a Before Change event on the left-hand side.
Now I am in my Data Macro. There is a dropdown here for me to choose my fields, and I'll choose SetField. The Name of the field that I want to set is DateModified. Notice as I start typing, it auto fills, and all I have to do is press Tab. The Value that I want is going to be Now with an Open and Close parentheses. Next, I need to enter my TimeModified. I could create another SetField step. But an alternative is to Ctrl+Click, and drag the step that I created already.
Notice now my cursor has a Plus sign. It's now duplicated my macro step for me, and I can change the word Date to Time. Now be careful, not to click on the auto fill here, because otherwise you'll get TimeModifiedModified. We'll go ahead and close the macro. When it asked to save it, definitely say Yes. So now I have a Data Macro that before I make a change to the record, it will update these fields. Let's test it out. I'll change the Cost of my first oil to $3.40.
When I Tab passed it, and go to the next record, it automatically fills in for me, the DateModified and the TimeModified. Now here is something to think about. It's good database design. Not to do data entry in tables, but in forms instead, and this is a perfect example why. I have to Tab passed these fields, and even though I have them set to auto fill, I run the risk of thinking that I need to enter it in myself, which increases the possibility of inappropriate data. If I work on the same data from inside of form, I can set it to skip over these fields completely as I tab through my data.
To show you what I mean. Scroll down in the Navigation pane to the Products Split Form and open it up. This form has one product above, and all the products listed below. The first thing that I need to do is modify it to include our new fields. So I'll right- click on the Tab, and change it to Layout View. Go up to the Ribbon on the right-hand side, and click Add Existing Fields. I'll look on my Products table, Find DateModified, and drag it down below Profit. I'll scroll down a little bit.
Drag TimeModified down below DateModified. Now I notice that these two fields are cut-off. So I'll click on one of them on the left-hand side, hold my cursor over the right-hand edge, and drag it. So it's a little bit bigger. Now go up to the right-hand side of your Ribbon and choose Property Sheet. Click on the Date field, and Shift+Click on the Time field. Go over to the Other tab, and change the Tab Stop to No. That way, when I tab through my field, it'll go right from Profit to the next record, and skip Date and TimeModified altogether.
We'll see that in a demonstration after the next steps. Now there is one more consideration. The Before update Data Macro we created was tied specifically to the Products table. To update time and date fields in all of our tables and forms, we'd have to re-create the macro in every single object. Instead, we could make it a stand- alone macro that does the exact same thing. So a huge benefit of doing the macro as a stand-alone Date/Time macro is that we can reuse it to update the date and time of every change in any form or object in the whole database.
So to re-create the macro as a stand- alone, go up to the Create Ribbon and to Macro on the far right-hand side. Now because we're working in a stand-alone macro, when I scroll down, SetValue is not on my list. I have to go up to the Ribbon and choose Show All Actions in order to get the command that I want. I'll go back down, and there is SetValue. I'll click on it. Our Item is going to be DateModified, and our Expression just as before is going to be Now with an Open and a Close parenthesis.
Like before, I can Ctrl+Drag, Copy the macro step, and change the word Date to the word Time. By doing it this way, I can create DateModified and TimeModified fields in every single one of my tables, and then call on this macro, and it will always update that field in that particular table. I'll close my macro, and it'll ask to save it. I'll give it the Name DateTimeStamp. Now back in my form, I'll go back to my Property Sheet, and click on the Events tab, and here I have an option for our Before Update macro.
I'll choose DateTimeStamp. This is the step that you can use in any form to call on the timestamp action. Let's test it out. Go back to your Form View. I'll click on the next product Alberquina 16, and I'll make a change to it. I'll change my Price to $8.35. Now when I tab for my Profit, it jumps straight to the next record. It skips over my DateModified fields. But as you can see below, it filled it in for me.
Using Before Update either as a Data Macro or as a stand-alone Macro will allow you to track when changes are made to your data. This brings a whole new level of data management to your database.
There are currently no FAQs about Access 2010 Power Shortcuts.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.