CODEWITHSUNDEEP
ms-accessvba
Smandoli
Smandoli

Reputation: 7019

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

The routine below in Access 2003 produces error 7961 -- My database 'can't find the module.' But it only does it on some modules. It is consistent on which modules fail.
What's wrong? 

Private Sub DoReplace()
    Dim obj As AccessObject
    For Each obj In CurrentProject.AllModules
       Debug.Print ModuleType(obj.Name) & "  " & obj.Name
    Next obj
End Sub

Public Function ModuleType(ByVal ModuleName As String) As Variant
On Error GoTo errHandler
    Dim mdl As Module
    Set mdl = Modules(ModuleName)
    ModuleType = mdl.Type
    ModuleType = Switch(ModuleType = 0, "std     ", ModuleType = 1, "class   ")
    Set mdl = Nothing
errExit: Exit Function
errHandler:
        ModuleType = "Err " & Err.Number  '7961
        Resume errExit
End Function

Debug output:

Err 7961  vba_28_Part_Asterisk
class     cls_22_mas90_Item2
Err 7961  vba_44_Part_WhereUsed2
Err 7961  cls_22_JobOps_BOM_WhereUsed_method2
Err 7961  vba_26_Part_misc
std       vba_44_Part_MRP
std       vba_99_TurnOffSubDataSheets
Err 7961  vba_99_MasteringArraysByScott
Err 7961  vba_44_Part_WhereUsed
Err 7961  cls_22_JobOps_BOM_WhereUsed
Err 7961  cls_22_mas90_Item
class     cls_22_mas90_Order
class     cls_23_HOMER_Item
class     cls_44_mrp_record

Upvotes: 1

Views: 4200

Answers (3)

David-W-Fenton
David-W-Fenton

Reputation: 23067

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.

Upvotes: 6

Smandoli
Smandoli

Reputation: 7019

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.

Upvotes: 1

Smandoli
Smandoli

Reputation: 7019

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.

Upvotes: 1

Related Questions