VBA error with modules: 'can't find the module'

Answer 1

Set mdl = Modules(obj.Name)

To do the above, the module object has to be loaded. This is checked using

obj.IsLoaded

(obj is an AccessObject, mdl is a Module -- refer above.)

My problem was that only some modules were loaded. Loading a module can be accomplished by opening it in the VBA IDE. No more Error 7961.

I am very surprised how difficult it was to get to this answer. I had to hammer on Google and apply some grit of my own. Hope it will help somebody else.

Answer 2

I know you've figured out the issue, but you don't appear to have grasped why it works this way, nor come up with the best approach to solve your problem.

In all versions of Access, there are several collections that you use all the time:

• TableDefs
• QueryDefs
• Forms
• Reports

and others that aren't used so often:

• Macros
• Modules

The first two collections are accessible only as members of a database, so you can use the TableDefs or QueryDefs collection only via something like this:

  CurrentDB.TableDefs.Count

This is because TableDefs and QueryDefs are pure Jet objects, rather than Access objects.

The other collections are collections of Access objects, and they include only the OPEN objects, as you can see if you do this:

  ?Forms.Count

...in the immediate window, you get 0 if there are no forms open, regardless of how many forms there are actually in your database.

Before Access 2000, you had to use the Documents container to get to the list of stored Access objects that weren't loaded. This was rather convoluted, and had to be approached differently for different object types. For modules, here's the code:

  Dim db As DAO.Database
  Dim cnt As Container
  Dim doc As Document

  Set db = CurrentDb
  Set cnt = db.Containers!Modules
  For Each doc In cnt.Documents
    Debug.Print doc.Name
  Next doc
  Set doc = Nothing
  Set cnt = Nothing
  Set db = Nothing

And you also had to know that Macros were stored in a container called "Scripts." Pretty ugly.

In Access 2000, because of the change to the way the Access project was stored (as a single BLOB field in a single record in a system table, instead of being stored in multiple records, one per object), the CurrentProject.All**** collections were introduced. These were:

• CurrentProject.AllDataAccessPages
• CurrentProject.AllForms
• CurrentProject.AllMacros
• CurrentProject.AllModules
• CurrentProject.AllReports

For your purposes, the best choice is clearly the AllModules collection instead of the Modules collection because that way you don't have to worry whether the modules are open or not. Of course, the Containers/Documents approach works, but AllModules requires significally less code.

EDIT:

The code for using AllModules is:

  Dim i As Integer

  For i = 0 To CurrentProject.AllModules.Count - 1
    Debug.Print CurrentProject.AllModules(i).name
  Next i

OR:

  Dim obj As Object

  For Each obj In CurrentProject.AllModules
    Debug.Print obj.name
  Next obj
  Set obj = Nothing

I always prefer using strongly typed objects for my FOR loops, but only a generic object variable works here, so I would probably use the counter since it saves having to clean up the last implicit object pointer at the end.

Also, keep in mind that the Modules collection (i.e., open modules) includes form modules as well as standalone and class modules, while AllModules is limited to standalone and class modules.

Answer 3

The solution is to pass the AccessObject (instead of a string). So from this:

ModuleType(ByVal ModuleName As String) As Variant

... to this:

ModuleType(ByVal obj As AccessObject) As Variant

David's post helped me to re-examine the code and also provided good general knowledge, and I'm up-voting his answer accordingly. My error, however, was about objects and functions rather than how to use the All collections.