Microsoft Access Database Tutorial VBA Code Task Tool To Outlook

Microsoft Access Database Tutorial VBA Code Task Tool To Outlook

It’s very strange how Microsoft Access database systems and processes constantly repeat themselves and once again I’m working on some VBA code to communicate with Microsoft Outlook (Email and contact management system).

Here you can see a quick video of such a simple but effective utility I offer (for only the cost of a couple of pints of beer!) – check it out…

Microsoft Access Database Tutorial VBA Code Task Tool To Outlook

The Task Tool utility was built to demonstrate the ease of use and how MS Access VBA code can communicate with Continue reading “Microsoft Access Database Tutorial VBA Code Task Tool To Outlook”

Concatenating Multiple Rows Into Single Line In MS Access

Microsoft Access Database: Concatenating Multiple Rows Into Single Line In MS Access

Now I’m not going to take credit for this next piece of Microsoft Access database VBA code as it was originally created by a well-known Allen Browne – many thanks!

Instead, I’m going to borrow his code for you (my regular readers) just in case you haven’t seen this before and add an element of quickly adding criteria to a query as a workaround to avoid expanding the VBA code itself.

concatenating multiple rows into single line in ms access

There are potential problems and errors this code will throw at you if you try to add criteria in the normal manner or embed any SQL directly into the calling custom function which means using some common sense and logic to deal with the challenge here.

Microsoft Access Database: Concatenating Multiple Rows Into Single Line In MS Access

First of all, let’s get the function to work and the query understood. In a new module, you will need Continue reading “Concatenating Multiple Rows Into Single Line In MS Access”

Access VBA Events: What Are Events And How Do We Use MS Access VBA Events

Access VBA Events: What Are Events And How Do We Use MS Access VBA Events

Microsoft Access VBA events have been around forever! Well, not forever but since VBA has been (circa 20+ years) and it’s still widely unexplored beyond the basic event triggers one gets to see and know about namely the clicking of a Command Button on a form or when the form itself opens.

Many users and developers have used events in many ways which is not just exclusive to MS Access (Excel is also a very popular application) and to get a feel for how one could use an event, check out this six minute video tutorial which runs through the After Update event to calculate a total from two other field controls in a form…

The video demonstrates how you can create VBA code and attach it to predefined event signatures that each control and object hosts. One could argue however why Continue reading “Access VBA Events: What Are Events And How Do We Use MS Access VBA Events”

Access Report VBA: Using Some VBA Report Properties To Automate Your Microsoft Access Reports

Access Report VBA: Using Some VBA Report Properties To Automate Your Microsoft Access Reports

Working and automating an Access report VBA will require some knowledge of the module window (VBE), the defined sections and an insight to pre-defined events for each element of a report design interface.

However, let’s take a step back and identify some of the report properties that can be used in a module that do not appear in a standard design view layout for a report. The three I want to highlight here and you may want to note are:

  1. PrintSection
  2. NextRecord
  3. PrintCount

In fact, to see a full list of report properties, open the Object Browser window (F2 on the keyboard) via the Access VBE window.

access report vba

These hidden properties can be manipulated only during the Access report run time which means the report Continue reading “Access Report VBA: Using Some VBA Report Properties To Automate Your Microsoft Access Reports”

MS Access Functions: How To Build A Soundex Or Sounds Like Custom Access VBA Function

MS Access Functions: How To Build A Soundex Or Sounds Like Custom Access VBA Function

Working with MS Access functions (of the built-in variety), you may have noticed a vast list of functions across different categories. By combining (or nesting) functions, you can certainly produce complex output values which will serve you well with your queries, forms and reports.

However, there is one lacking feature (or function) in Microsoft Access that could hinder searching for values and that is a function that can find similar or ‘sounding like’ a string match of characters. This function is widely known as ‘Soundex’ and some database applications do provide their own algorithm to support this technique (including the Soundex SQL Server function).

ms access functions soundex

The algorithm is a phonetic list of character strings based on the pronounced English language and of course this Continue reading “MS Access Functions: How To Build A Soundex Or Sounds Like Custom Access VBA Function”

MS Access Databases Properties: How To Set It Using Access VBA And The DAO Object

MS Access Databases – Properties: How To Set It Using Access VBA And The DAO Object

As with most Microsoft Office applications, MS Access databases also have a properties feature that captures the basic file attributes like author, date and subject to name a few. There is also a custom tab to edit and add your own properties and is stored with the database file itself.

To know more about changing the built-in properties window, see view and change MS Office 2010 file properties. For the earlier versions (pre 2007), this feature can be found via the File menu.

ms access databases properties

In this article, I want to introduce some Access VBA code to manage and control custom properties instead which is Continue reading “MS Access Databases Properties: How To Set It Using Access VBA And The DAO Object”

Microsoft Access Forms: Working With The KeyPreview Property

Microsoft Access Forms: Working With The KeyPreview Property

Working and managing data via Microsoft Access forms is only as good as the design and if the user becomes frustrated then don’t expect your database (and you) to receive any credit.

One element overlooked when form designing which is easily missed to be fair, is the lack of keyboard short-cuts should users wish to throw away that pesky ‘rodent’ mouse! After all, we end up enter and managing the data via the keyboard in any case.

microsoft access forms keypreview property

As you may have gathered by now, I’m a keyboard fan and only use the mouse for the obvious interactions and even though Continue reading “Microsoft Access Forms: Working With The KeyPreview Property”

Access CurrentUser Function: Are There Any Benefits Here?

Access CurrentUser Function: Are There Any Benefits Here?

Some of my students during a training session recently discovered the Access CurrentUser function listed from the Expression Builder tool which is one of the predefined members of the MS Access ‘built-in functions’ category and assumed it would return the currently logged on user (via a network) but in all cases it showed as ‘Admin’.access currentuser

So why bother using this function at all? Well, it’s really a legacy function from the throwback to the earlier format ‘MDB’ files which used the Workgroup Security utility (and has now been dropped with the later ‘ACCDB’ versions).

Also, every time you started your Access database, the system generates a same named locking file (ldb/laccdb) which stored one or more of the machine’s name (multi-users) but not the actual user so this is really not the same thing. So how do you Continue reading “Access CurrentUser Function: Are There Any Benefits Here?”

Access Forms VBA: IsLoaded Property Check

Access Forms VBA: Using The IsLoaded Property

Most of the time when developing with MS Access to achieve a really smooth look and feel to your database you will need to eventually code your application and one area where you should start would be with Access forms VBA since end-users will be operating your proud achievements and want to make this a really easy experience for the novice.

With my clients, they like to see something very early on in the development cycle and even though I still need to follow my own planning and designing methodologies, I soon lean towards the front-end namely forms so that my client feels that I’m providing value for money and they can see something really tangible.

If I think of the many times I’ve had to counsel clients during the building phase without seeing any real impact in terms of nice looking screens and other tangible objects – I probably should of added some life coaching time too prior to the project!

Access Forms VBA: IsLoaded Property Wrapped In An If

access forms vba codeOne aspect of Access forms VBA is to test to see if a form is actually open (or loaded) before interacting with it as it will cause errors. One way to validate this is to introduce the IsLoaded property.

Since you want to test it too, you will need to wrap it into a standard Continue reading “Access Forms VBA: IsLoaded Property Check”

How To Convert Numbers To Words: Access VBA Function

How To Convert Numbers To Words: Access VBA Function

Once again, I’ve been asked by a client to create procedure to turn a monetary number (numerical value) into its text equivalent and so I wanted to share with you how to convert numbers to words using Visual Basic code that can also be applied not just to your Access database but with any application that entertains VBA code! how to convert numbers to words

How To Convert Numbers To Words: The Steps

This article is intended for users who know and have some VBA code knowledge but need a little push to Continue reading “How To Convert Numbers To Words: Access VBA Function”