Monthly Archives: January 2018

How to solve missing intellisense in MS-ACCESS module

At times the helpful  ‘auto-complete’ feature seems to disappear. This can happen at the worst times and be confusing.

The intellisense feature. or ‘intelligent code completion’ is a feature that is very helpful for application developers and computer programmers.  It gives the developer the expected parameter arguments and types of data that are needed to correctly run built in or custom made functions.  For example, in the below vba code example, the intellisense feature helps a programmer to complete the debug function.

Intellisense - autocomplete example

Intellisense helps programmers know how to call funtions and sub routines.

However at times,  the intellisense feature fails to work. To make things more consfusing, Ms-access does not overtly indicate there is a problem. What causes intellisense to stop auto-completing?

The reason why autocomplete (also known as ‘intellisense’) fail is due to compile error in the code. While MS-ACCESS detects the problem the user often is not aware of the problem. For example, look a the following code; it has an error in it. Did you catch it? The dim statement is missing from the variable declaration.

missing Dim statement in VBA code

The code has a missing ‘Dim’ statement. It should precede the declaration of a variable with the ‘Dim’ statement. This will prevent the code from compiling.

There are exceptions. I’ve noticed that while the debug.print functionality continues to have intellisense capability, some string and date functions, e.g. the dateadd and mid functions) do not.  For example, when I tried to use the dateadd function, intellisense did not work. Meanwhile, debug.print appears to work fine. This can cause confusion because it appears to work but in some cases doesn’t. As a matter of course, I’ve learned to frequently compile the code during development work. Any time intellisense fails to work should be warning sign that something is wrong.

To confirm that the issue is caused by the compile error, in the vba development environment select from the menu ‘debug’ and then ‘compile’ as shown below. If there is a compile error it will display the error.

MS-Access compile via the debug – compile menu item.

in this case, lacking a Dim statement, it generated an error complaining that the statement is invalid outside the type block.

The error thrown is “Compile error: Statement invalid outside of Type block.”

After the error is fixed by adding the Dim statement to the line of code, the compile succeeds and intellisense will, to the relief of the programmer, again work as expected.