How to Create an Autokeys Closing Macro in Microsoft Access (2010)

We'll walk you through creating a custom hotkey for Microsoft Access 2010 using an autokeys macro. The macro we'll show you is designed to avoid a problem where incorrectly closing an object can save filters and sorts into their properties and alter their behaviour in the future.

Music:
Disco con Tutti - Kevin MacLeod
http://incompetech.com/
---------------------------------------------
If you found this video helpful, please consider giving it a like, leaving a comment or sharing it.

Get more help and advice on Excel, Access or VBA projects:
http://www.software-matters.co.uk/youtube_links.html

Visit the Software-Matters website:
http://www.software-matters.co.uk/

Software-Matters is based in Gillingham, Dorset, in the south-west of the United Kingdom (UK), near Somerset, Wiltshire and Hampshire and the cities of Bournemouth, Poole, Southampton, Bristol, Bath and Salisbury.
---------------------------------------------
TRANSCRIPT:

Not everyone knows that there is a right way and a wrong way to close objects in Microsoft Access. We'll show you where hidden errors could be creeping into your database and demonstrate a quick and easy way to keep the problem at bay using an autokeys closing macro.

The Problem:
When a user of an Access database closes an object such as a form, any active filters or sorts on the data are automatically saved into the properties of the form. When you come back to view the form again, those filters will still be there, and can lead to unexpected behaviour.

For example, if the filter you were using references another open form, if the system tried to apply that filter while the other form isn't open, it will cause an unexpected error to interupt the user.

There is a solution to this problem that allows you to close windows without the default behaviour of sort and filter properties being saved, and it uses Access' macro system.

The Solution:
We can add an autokeys macro to our Access system that is programed to close the object currently being viewed in a safe way. First we need to choose a key to do this. Note that any function the key already has will be overwritten by the upcoming macro, so pick one that doesn't do anything you're interested in. We use F10, since pressing Alt has the same function.

To begin, head to the 'Create' tab of the Ribbon and look to the far right. Clicking on the 'Macro' icon will start the new macro. By default it will be called 'Macro1', but we need to rename it 'Autokeys' when we save it later to tell the system that this macro will contain new instructions on what functions our hotkeys should perform. Any other custom hotkeys you make should also be defined within this macro.

You'll see the macro is asking you to Add a New Action. From the dropdown list it provides, select 'Submacro'. Our macro will contain a Submacro for each key we want to give a function. The submacro will be called Sub1. Rename to the name of the key you want to use to close objects enclosed in curly brackets.

Below the name we can add an action to this submacro, and thus define what should happen when this key is pressed. From the list of actions, pick 'Close Window'. You'll see new options appear: Object Type, Object Name, and Save. If we wanted our macro to only close a specific type of access object, like only tables, we could say so in the first box. Similarly, the second box would let us only close objects with given names.

The save box is where the root of the solution to our closing problem lies. You simply want to choose 'no' to saving. Now save this whole macro, making sure to call it Autokeys, and your chosen key will start closing objects.

Now if you still have more to develop in your database, you won't really want to use this new hotkey too much, because of the fact it is especially set up not to save information. So if you made changes to a form in design view and then pressed this key, the form would close and undo your changes without any warning!

So know you know both how to avoid this specific problem, and how to set up custom hotkeys, a skill you can use to make many commonly performed operations just a keystroke away at any time. We hope you found this information useful. If you're interested in more Mircosoft Office and VBA tips, subscribe to this channel and visit us at our website: software-matters.co.uk. Thanks for watching.

Видео How to Create an Autokeys Closing Macro in Microsoft Access (2010) канала Software-Matters