How to Create an Autokeys Closing Macro in Microsoft Access

How to Create an Autokeys Closing Macro in Microsoft Access

As a follow on from my last post about Microsoft Access Macros, here’s a good working example clearly explained using the ‘unsafe’ CloseWindow command…

It walked you through creating a custom hotkey for Microsoft Access (using version 2010 though it will also apply through to the current version, 2016) which used the ‘Autokeys’ macro.

This macro shows you how to avoid a problem during a design and run-time for a form where incorrectly closing this object can save filters and sorts into their properties and alter their behaviour when re-visiting the form.
Continue reading “How to Create an Autokeys Closing Macro in Microsoft Access”

Microsoft Access 2016 Macros: Show All Macro Commands

Microsoft Access 2016 Macros: Show All Macro Commands

The great debate of whether to use Microsoft Access macros or the higher level of VBA code will linger on until the end of time (or realistically, Access is discontinued)!

In the meantime, most Access developers will continue to lean towards Access VBA but the majority of power users, self-taught Access designers will still embrace the ease of g using macros to automate their databases.

Microsoft Access 2016 Macros Show All Macro Commands

With the later versions of MS Access (post 2010), macros have become more powerful and flexible and with the added introduction of better web integration and the fact VBA Continue reading “Microsoft Access 2016 Macros: Show All Macro Commands”

Microsoft Access Databases: Pros & Cons of Access Macros

Microsoft Access Databases: Pros & Cons of Access Macros

Here’s an snippet from my popular Microsoft Access database eBook on How To Use Microsoft Access Macros & Automate Your Application 

In this section I’m going to list the advantages and disadvantages of using Access macros that is an exercise developers need to carry out to determine how they will programme their application going forward and avoid the dreaded result of having a ‘pear shaped’ and inflexible database.

access macros ebook cover

Benefits of using Access Macros

Here’s the list:

1. Easier to write! You do not have to have a university degree in computer programming to understand and utilise macros. The command reference is intuitive and easy to apply. It just requires some investment of your time.

2. Disabled Mode – From version 2007 by default, any database opened that contained VBA code would not run as part of the security changes made to Microsoft Office applications and prevent unwanted macro virus threats that the VBA code could contain some malicious routines which some programmers seem to get a kick out of! Macros used within templates in Access are safe and run in normal mode.

3. Access Services – With the introduction of SharePoint server where you can now publish your Access database on the web in a secured environment, VBA code is Continue reading “Microsoft Access Databases: Pros & Cons of Access Macros”

Microsoft Access Database 2013 Splash Screen Form

Microsoft Access Database 2013 Splash Screen Form

Back in 2012, I posted an article on how to create a splash screen form without VBA code too using a little trick to name your database with an .bmp extension to it – a hidden gem!

However, in this quick video tutorial, the alternative way is to create a navigation form in Microsoft Access (from version 2010) and take advantage of the Access options and a simple macro...take a look…

Excuse the music and text narratives but hopefully you should have been able to follow the prompts?

There are other Access form options and properties to set to help present a smoother and more polished form including modal, pop-up Continue reading “Microsoft Access Database 2013 Splash Screen Form”

How to Convert Microsoft Access Macros To Visual Basic For Application VBA

How to Convert Microsoft Access Macros To Visual Basic For Application VBA

The scenario: You’ve inherited an Access database that was originally built way back say with version 97 and some of the forms have started to look a little tired (which is not an issue) and you discovered that when opening the database there are over 250 separate macro procedures which most were only useful for a single form.

What do you do? You certainly need to consider either tidying up the macros, grouping them in to some sort of logical sequence; reducing the number of Access objects, possibly embed them into your forms (which the newer versions tend to prefer) or convert Microsoft Access macros to Visual Basic using the converter tool.

Here’s a quick video tutorial I found which shows you how to convert a macro…

How to Convert Microsoft Access Macros To Visual Basic For Application VBA

You may wish to note that any external macro objects (that’s a macro which is found in the Navigation Pane) will be Continue reading “How to Convert Microsoft Access Macros To Visual Basic For Application VBA”

Access Macros 2010: How To Use Data Table Macro Triggers

Access Macros 2010: How To Use Data Table Macro Triggers

If you are currently using Microsoft Access 2010, you will probably have seen the new data Access Macros 2010 for a table which there are 5 different events to choose from.

Take a look at this video tutorial (which is less than 5 minutes) demonstrating how you can set and apply a data macro to a table and in this case using the ‘Before Change’ event.

Access Macros 2010: How To Use Data Table Macro Triggers

There are therefore three types of macros available to this version making Continue reading “Access Macros 2010: How To Use Data Table Macro Triggers”

Using The Message Box in VBA And Access Macros

Using The Message Box in VBA And Access Macros

If you know anything about Access databases and that data is automatically bound to either a table or a simple query meaning data is saved to disk then on some occasions you may want to control this action. Using the message box in VBA or as a macro procedure can intercede and prompt users before committing to disk.

This is just one example of a prompt you can add and call to your database which will help those workflows and the smooth running of the database application. Having a message prompt appear at strategic moments will add the extra layer of control.

Using The Message Box in VBA And Access Macros – Two approaches…

Users can create a message box in VBA (known as MsgBox) or as a macro which is a predefined alert prompt with a series of Continue reading “Using The Message Box in VBA And Access Macros”

How To Open Access In Full Screen: Opening A Form As Maximized

How To Open Access In Full Screen: Opening A Form As Maximized

open access in full screenOnce users get a grip on form design techniques in Microsoft Access database, they normally want to jump ahead and customize forms in their applications that appear polished and professional. One technique they can use is to open Access in full screen view which hides other no essential screens keeping your database neat and tidy.

It requires a combination of setting various form properties during the design time mode and optionally (but ideally) applying either macros or VBA code to the form’s module too.

The final touches to this process would be to create a desktop icon shortcut to load the database file and run Continue reading “How To Open Access In Full Screen: Opening A Form As Maximized”

Access Macros: There Are Now 3 Types Of Microsoft Access Macros (2010)

Access Macros: There Are Now 3 Types Of Microsoft Access Macros (2010)

With the latest version of Microsoft Access (2010), there are now 3 types of Access macros developers can utilise and release the power of this application further.

The 3 types are as follows:

  1. Macro Objects
  2. Embedded Macros
  3. Data Macros

Macro objects are avialable to all versions of Microsoft Access but version 2010 has a new interface tool plus more key commands and functionality. It is where you store the general procedures to your database.

Embedded macros were introduced from version 2007 and now is the default procedure when creating event driven procedures in forms and some reports.

Data macros which is exclusive to Access 2010 is the new and powerful to trigger procedures at the data level (via a Table) which is similar to Triggers in SQL Server.

Access Macros: There Are Now 3 Types Of Microsoft Access Macros (2010)

access macros ebook coverTo learn more about these 3 types of Access macros, why not take a sneak preview of my eBook by clicking on the link below or image to the right…

Understanding and Using Microsoft Access Macros – preview.

Access Requery Or Not To Requery – That Is The Question

Access Requery Or Not To Requery – That Is The Question

The following four methods can be found in Microsoft Access and have different uses:

  1. Access Requery
  2. Access Refresh
  3. Access Repaint
  4. Access Recalc

They can be split into two parts where the first two points handles records (the data) and the latter two the objects and their components (no data). Therefore you can start by answering the first simple question to help navigate to which part is best to use:

“Do I want to update data or components in my Microsoft Access database?”

access requeryAccess Requery Or Not To Requery – That Is The Question: The Definitions

Access Requery

Using the Requery method you are calling a complete reloading of all records from the underlying table or query, This means Continue reading “Access Requery Or Not To Requery – That Is The Question”