First Published 11 Oct 2025

Attached is a modified version of a repro file sent to me by a fellow Access developer, Shreyas Shah, who is based in the USA.

Its a fairly obscure bug with two workarounds and alternative code that works reliably. Its also not a new bug occurring in Access 2010 as well as the latest versions of 365.

I've reported the issue to the Access team, but its very low priority. I’m not expecting it to get fixed – I’m just puzzled as to why it fails.



Download:

The attached zip file contains two databases: TestDb and LibraryDb.

    Module Name Type Test     Approx 0.9 MB (zipped)



Steps to reproduce:

1.   First open LibraryDb and click each of the buttons on the form in turn to confirm all work correctly.
      The center & right buttons have slightly different code (see below) to get the same result with the right button code slightly faster.


2.   Now open TestDb which has LibraryDb as a reference library. A missing reference error message will appear:


      Fix the reference location for your computer. Click Database Tools | Visual Basic to open the Visual Basic Editor. Then click Tools | References to view the References dialog:


      Browse to the location for the LibraryDb and click OK

      Now restart Access – doing that is important.

      The TestDb form is identical but it links to the code in the other database.
      It also has an additional button Open VBE. Don’t click that button YET.


      Click each of the other 3 buttons in turn – the left & right buttons still work fine but the middle button shows error 9 (subscript out of range) in code line 100:


      Here is the full code for the module that errors. Line 100 is when it checks the module type:

Copy code
Public Function GetModuleTypesNames1() As String
      Dim ao As AccessObject
      Dim s As String
      Dim m As Module

10    On Error GoTo Err_Handler

20    For Each ao In Application.CurrentProject.AllModules

30        If Left(ao.Name, 5) = "Form_" Then
              'skip form modules
40        ElseIf Left(ao.Name, 1) = "_" Then
              'Skip template modules such as _Standard etc.
50        Else
60            If Not ao.IsLoaded Then
70                DoCmd.OpenModule ao.Name
80            End If

              Set m = Application.Modules(ao.Name)
              
100           If m.Type = 0 Then 'acStandardModule
110               s = s & m & ": " & Space(10) & "Standard Module" & vbCrLf
120           Else
130               s = s & m & ": " & Space(10) & "Class Module" & vbCrLf
140           End If

150       End If
160   Next

Exit_Handler:
170   GetModuleTypesNames1 = s
180   Exit Function

Err_Handler:
'185  If err= 9 Then Resume Next
190   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in line " & Erl & " of procedure GetModuleTypesNames (Module modModuleInfo)"
200   Resume Exit_Handler
End Function

Its NOT a timing issue - I’ve tested adding delays and that has no effect

There are 2 simple workarounds which allow the code to run without error:

a)   Bypass error 9. To do so, enable line 185 in the above code
b)   Open the Visual Basic Editor (VBE) before running the middle button code
      Close the VBE again - the middle button code may still work but sometimes errors

So my question is: why does it trigger error 9 when the VBE is closed but run fine when it is open (or has been opened)?



The alternative working version used on the right button is VBE extensibility code but using late binding:

Copy code
Option Compare Database
Option Explicit

'constants used for late binding in place of VB Extensibility
Const vbext_ct_StdModule = 1
Const vbext_ct_ClassModule = 2

Public Function GetModuleTypesNames2() As String
      Dim ao As AccessObject
      Dim s As String

10    On Error GoTo Err_Handler

20    For Each ao In Application.CurrentProject.AllModules
30        If Left(ao.Name, 5) = "Form_" Then
              'skip form modules
40        ElseIf Left(ao.Name, 1) = "_" Then
              'Skip template modules such as _Standard etc.
50        Else
60            If Not ao.IsLoaded Then
70                DoCmd.OpenModule ao.Name
80            End If

              'new code
90            Select Case Application.VBE.VBProjects(GetOption("Project Name")).VBComponents(ao.Name).Type
              
              Case 1 'vbext_ct_StdModule
100                 s = s & ao.Name & ": " & Space(10) & "Standard Module" & vbCrLf
110           Case 2 'vbext_ct_ClassModule
120                 s = s & ao.Name & ": " & Space(10) & " Class Module" & vbCrLf
130           End Select
140       End If
150   Next

Exit_Handler:
160   GetModuleTypesNames2 = s
170   Exit Function

Err_Handler:
180   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in line " & Erl & " of procedure GetModuleTypesNames (Module modModuleInfo)"
190   Resume Exit_Handler
End Function

As this code runs successfully from a library database both without error and is slightly faster, this is clearly the best solution in this case.



Feedback

Please use the E-Mail button in the contact form below to let me know whether you found this article useful or if you have any questions.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 11 Oct 2025

Return to Bugs & Fixes Page

Return to Top