Curious: Module not found

Question

I have a routine that can inspect in what application a general routine (from a library database) is called with what arguments.

For this purpose I loop through all applications. For each application I grab the module_names from  MSysObjects WHERE ParentId = -2147483646.

Then - in an opened new application - I address the different Modules using:

        Set cur_module = cur_app.Modules(module_name)

This works OK for the majority of the modules, but not for all: an error is generated saying that the module could not be found.

Re-running the routine shows everytime the same missing modules. These “missing” modules seem randomly. Some applications have 0 missing modules, some 5. Both old applications and younger ones can have missing modules.

Renaming a module does not help.

Compile the application and compact and repair does not help either.

The only thing that seems to work is to add an empty line to the module.

What is happening, or has happened, here?

Imb.

Edit: all applications run normally!

Answer 1

Hi,

Not sure if it will help but you might consider posting the code that's causing an issue just in case someone can spot something that could explain why you're seeing that behavior.

Just a thought...

Answer 2

Hi,

Not sure if it will help but you might consider posting the code that's causing an issue just in case someone can spot something that could explain why you're seeing that behavior.

Just a thought...

Hi DB guy,

In my opinion it has nothing to do with the code as such, because the majority of the modules is "found". But why there are some exceptions? It has something to do with the modules itself, because just adding a blank line seems to make the module "found" again.

Imb.

Answer 3

You might try deleting any modules in all your "applications" that have nothing in them.

Answer 4

If you duplicate the module, does the duplicate show up?

Are the missing modules class or standard or both?

Also I think not all modules have WHERE ParentId = -2147483646. But I can't remember where I read this. If you take out the Where, do all the modules show?

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 5

Are the modules missing after the WHERE search or only when you do the SET?

Answer 6

Are the modules missing after the WHERE search or only when you do the SET?

Hi ATGNWT,

Just at the moment of the Set statement to get the Module object:

            Set cur_module = cur_app.Modules(mod_naam)

'mod_naam' is taken from the MSysObjects table.

Imb.

Answer 7

According to Access Help that property shows only open objects. Are the objects open? If they are all open somehow can you loop thru them via the index, e.g. .Modules(0), .Modules(1)

Answer 8

You might try deleting any modules in all your "applications" that have nothing in them.

Hi Lawrence,

If you look in the Database window, all modules are there, and all modules have code. In fact all code in the modules work fine, because the application works fine.

Only when I try to inspect the module from outside, using the module name form MSysObjects, and use that name to define the module object, many times this is successful, but sometimes an error occurs saying "Module not found".

If I re-run the procedure, the modules that are "found" or "missing" are consistently. That is, when it is missing, it is also missing the next time, and the same holds for the "found" modules.

Imb.

Answer 9

If you duplicate the module, does the duplicate show up?

Are the missing modules class or standard or both?

Also I think not all modules have WHERE ParentId = -2147483646. But I can't remember where I read this. If you take out the Where, do all the modules show?

Hi Chris,

All modules are standard modules. And with the ParentId = -2147483646 I catch them all, just the same modules that are visible in the Database window.

There is nothing wrong with the modules itself, because all the applications run perfectly. Only when inspecting the modules from outside (see my answer to Lawrence) some of the modules are "missing".

Answer 10

According to Access Help that property shows only open objects. Are the objects open? If they are all open somehow can you loop thru them via the index, e.g. .Modules(0), .Modules(1)

Hi ATGNWT,

As far as my knowledge goes, this holds for the Forms collection (and probably Reports, but I am not so familiar with Reports).

Modules look different. You can construct a loop as:

    For Each cur_module in cur_app.Modules
        ....
    Next

Syntactically this is correct, but nothing happens with the statements inside the loop.

Imb.

Answer 11

A thought occurred to me.

These are standard modules.

Are these standard modules for Forms or Reports?

If so are the "Has Module" property set to Yes on the Forms or Reports?

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 12

These are standard modules.

Are these standard modules for Forms or Reports?

Hi Chris,

The modules are standard modules, containing Subs ans Functions for application-wide use.

Imb.

Edit: not belonging to Forms or Reports.

Answer 13

Not sure if it will help but you might consider posting the code that's causing an issue just in case someone can spot something that could explain why you're seeing that behavior.

Hi DB guy,

I prepared an example to show the problem. It contains same the two basic routines "Get_modules" and "Pak_module". The surrounding Sub Show_problem is modified to give a better view of the problem.

Function Get_modules(cur_dbnaam As String)
  Dim tmp_db As Database
  Dim module_set As Recordset
  Dim cur_text As String
  
    Set tmp_db = DBEngine.Workspaces(0).OpenDatabase(cur_dbnaam & ".mdb")
    
    Set module_set = tmp_db.OpenRecordset( _
                     "SELECT * FROM MSysObjects" _
                   & " WHERE ParentId = -2147483646")
  Do While (Not module_set.EOF)
    cur_text = cur_text & module_set!Name & "/"
    module_set.MoveNext
  Loop
  
  Get_modules = cur_text
End Function
'--------------------------------------------------------------------------------------------------


Function Pak_module(cur_app As Application, mod_naam As String, cur_module As Module) As Boolean
  On Error GoTo stop_it
  
  Set cur_module = cur_app.Modules(mod_naam)
  Pak_module = True
  Exit Function
  
stop_it:
End Function
'-------------------------------------------------------------------------------------------


Sub Show_problem(cur_form As Form)
  Dim cur_set As Recordset
  Dim cur_app As Application
  
  Dim all_modules As String
  Dim mod_naam As String
  Dim cur_module As Module
  
  If (Not Open_file1("Show_problem", "rtf")) Then Exit Sub
  
  Outln Speciaal("\b ", Quoted("Show problem")), Make_datum(Date), ""

  Make_table "lll", "4/6/4/", Shading(25)
  Fill_row "Database", "Module", "Resultaat"
  Make_table
  
  active_sql = "SELECT * FROM DB_tbl" _
             & " WHERE SElectie = TRUE" _
             & " ORDER BY DB_naam"
  Do While (Active_set(cur_set, 5))
  
    all_modules = Get_modules(cur_set!DB_naam)
    
    Set cur_app = GetObject(cur_set!DB_naam & ".mdb", "Access.Application")
    cur_app.Visible = False
    Do While (all_modules > "")
      mod_naam = Split_string(all_modules, "/")
      
      If (Pak_module(cur_app, mod_naam, cur_module)) Then
        Fill_row cur_set!DB_naam, mod_naam, "OK"
      Else
        Fill_row cur_set!DB_naam, mod_naam, R_bold("Not found")
      End If
    Loop
    
    cur_app.CloseCurrentDatabase
    
  Loop
    
  End_table 1
  Close_file
End Sub

Running "Show_problem" results in the next overview:

"Show problem"

08-okt-2015

Database	Module	Resultaat
Archief	A_opslaan	OK
Archief	Algemeen	OK
Archief	Knoppen	OK
Archief	Question	OK
Archief	Sub_Doc_relaties	OK
Archief	Sub_Gebeurtenissen	OK
Archief	Sub_Nieuwe_gebeurtenissen	Not found
Archief	Sub_Op_een_dag	OK
Archief	Sub_Ouders_def	OK
Archief	Sub_Overlijden	OK
Archief	Sub_Partners	OK
Archief	Sub_Pers_relatie	OK
Archief	Sub_Voorouder	OK
Archief	Tijdelijk	OK
Crypto	A_opslaan	OK
Crypto	Knoppen	OK
Crypto	Question	OK
Crypto	Tijdelijk	OK
Eurogard	A_opslaan	OK
Eurogard	Algemeen	OK
Eurogard	Algemeen_xxx	OK
Eurogard	Definitie	OK
Eurogard	Knoppen	OK
Eurogard	Nieuw	Not found
Eurogard	Question	Not found
Eurogard	Tijdelijk	OK
Film	Algemeen	OK
Film	Knoppen	OK
Film	Question	OK
Film	Sub_film_relaties	OK
Film	Sub_Pak_url	OK
Film	Sub_vergelijk	OK
Film	Tijdelijk	OK
LOGO	A_opslaan	OK
LOGO	Algemeen	OK
LOGO	Knoppen	OK
LOGO	Mail	Not found
LOGO	Question	OK
LOGO	Sub_nieuw_lid	OK
Reube	Algemeen	Not found
Reube	Knoppen	OK
Reube	Maandrooster	OK
Reube	Question	OK
Reube	Sub_Begeleidingsrooster	OK
Reube	Sub_Dagrooster	OK
Reube	Sub_Logeerrooster	OK
Reube	Sub_Periode	OK
Reube	Tijdelijk	OK
VanHek_Interieur	A_Opslaan	OK
VanHek_Interieur	Algemeen	Not found
VanHek_Interieur	Befores	OK
VanHek_Interieur	Knoppen	OK
VanHek_Interieur	Question	OK
VanHek_Interieur	Sub_Bestelling	OK
VanHek_Interieur	Sub_Besteltraject	OK
VanHek_Interieur	Sub_Calc_plan	OK
VanHek_Interieur	Sub_Checks	OK
VanHek_Interieur	Sub_Copy_project	OK
VanHek_Interieur	Sub_Eenheidsprijs	OK
VanHek_Interieur	Sub_Regels_toevoegen	Not found
VanHek_Interieur	Sub_Relatielozen	OK
VanHek_Interieur	Sub_Samenvatting	OK
VanHek_Interieur	Sub_Sel_regels	Not found
VanHek_Interieur	Sub_Set_toevoegen	OK
VanHek_Interieur	Tijdelijk	Not found

The routine "Show_problem" contains a couple of references to a referenced library database.

For instance 'Open_file1' does all the handling to open a file for output, including error handling and supplying the header information for an rtf-file.

'Active_set' is the routine to loop through the set based on active_sql, including error handling and progress indication (the 2nd parameter).

'Fill_row' writes a row in a defined table ('Make_table').

 

I hope this gives you more insight in the occurring problem.

Imb.

Answer 14

I may not be the best at expressing what is in my mind so I'll ask two questions?

1. Are you trying to get a list of all modules whose parent ID = -214748646?
2. Are all module parent ID's -214748646?

I ask because in the db's I glanced at the Module ID = -214748646 where the Parent ID = some other value.

Also when I look through the table, Form Module have the Parent ID you mention which is the ID of Modules however Modules that are not modules of Forms have a different Parent ID.

My suggestion is to have a Where with both Parent ID's

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 15

I may not be the best at expressing what is in my mind so I'll ask two questions?

1. Are you trying to get a list of all modules whose parent ID = -214748646?
2. Are all module parent ID's -214748646?

I ask because in the db's I glanced at the Module ID = -214748646 where the Parent ID = some other value.

Also when I look through the table, Form Module have the Parent ID you mention which is the ID of Modules however Modules that are not modules of Forms have a different Parent ID.

My suggestion is to have a Where with both Parent ID's

Hi Chris,

In MSysObjects you have:

    Modules, with Id = -2147483646
    Forms, with Id = -2147383647

Objects with ParentId = -2147483646 belong to Objects
Forms with ParentId = -21474833647 belong to Forms

I am not interested in Forms, only in Modules.

With the Get_modules function (as specified earlier in this thread) I get exactly all the module names that are in that database. So in that respect all works perfectly, and it is sufficient the use only ParentId = -2147483646.

The strange thing is that the Module itself mostly can be found, but not always. And this is reproducible.

And my question is: Why cannot ALL modules be found?

Imb.

Answer 16

Thanks Imb,

That clears that up for me. I am trying to compile your code but I get an error on this line

  If (Not Open_file1("Show_problem", "rtf")) Then Exit Sub

---------------------------
Compile error:

Sub or Function not defined
---------------------------

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 17

I am trying to compile your code but I get an error on this line

  If (Not Open_file1("Show_problem", "rtf")) Then Exit Sub

---------------------------
Compile error:

Sub or Function not defined
---------------------------

Hi Chris,

Well, that is right. 'Open_file1' is a routine in my library database full of generalized routines, so it is not defined in your environment. The same holds for Make_table, Fill_row, Close_table, etc.

In fact, in the standard Access way you would make a Report to present these results, for instance about the behavior of finding modules. I do not use Reports, but instead I use a couple of routines to export the results to an rtf-file, readable by Word, Wordpad, ... (or csv, or xml, or html).
Generating reports in this way is - for me - far easier and far more flexible than the standard Access way.

But the essence is, I can find all Modules names in a database, but for some reason (which?) NOT ALL of the modules with these names can be found! Strange, isn't it?

Imb.

Answer 18

AllTheGoodNamesWereTaken is correct: The Modules collection only contains the modules that are open in the VBA editor.

I can reproduce your problem quite easily. The reason why the error goes away when you add a line to the module is that it is being opened in the VBE in this moment! It is not neccessary to add a line, just opening the module by double-clicking it is enough to make the error disappear.

You need to open the module explicitely before you can set a reference to it:

cur_app.DoCmd.OpenModule mod_naam
Set cur_module = cur_app.Modules(mod_naam)

should make your problem go away.

Matthias Kläy, Kläy Computing AG

Answer 19

AllTheGoodNamesWereTaken is correct: The Modules collection only contains the modules that are open in the VBA editor.

I can reproduce your problem quite easily. The reason why the error goes away when you add a line to the module is that it is being opened in the VBE in this moment! It is not neccessary to add a line, just opening the module by double-clicking it is enough to make the error disappear.

You need to open the module explicitely before you can set a reference to it:

cur_app.DoCmd.OpenModule mod_naam
Set cur_module = cur_app.Modules(mod_naam)

should make your problem go away.

Matthias Kläy, Kläy Computing AG

Hi Matthias,

Thank you for your reply. I will study it, but I am a little busy in the weekend.

If you are right then I would expect that it holds for ALL the modules per application. What I see is that some applications do not show the error at all without opening the module explicitly, while other applications show the error for some modules.

Imb.

Answer 20

> [...] What I see is that some applications do not show the error at all without opening the module explicitly, while other applications show the error for some modules.

Modules may be open or closed in the VBA editor depending on the developers actions, and the state of the module (open or closed) is saved over sessions (i.e., when you close and reopen the database).

Just make the following experiment: Open one of your databases, say "Archief", then open the VBE editor. Then *close* all modules by clicking on the close button in the upper right corner of each modules window (*not* the VBA editor window).

Then rerun your procedure. It will show you "Module not found" for *all* modules.

Edit "Archief" again, open one module by double-clicking it, and rerun yor procedure. It will "find" this module, but none of the others.

Again: Setting a reference to a module in the Modules collection requires that the module is open.

Matthias Kläy, Kläy Computing AG

Answer 21

> [...] What I see is that some applications do not show the error at all without opening the module explicitly, while other applications show the error for some modules.

Modules may be open or closed in the VBA editor depending on the developers actions, and the state of the module (open or closed) is saved over sessions (i.e., when you close and reopen the database).

Just make the following experiment: Open one of your databases, say "Archief", then open the VBE editor. Then *close* all modules by clicking on the close button in the upper right corner of each modules window (*not* the VBA editor window).

Then rerun your procedure. It will show you "Module not found" for *all* modules.

Edit "Archief" again, open one module by double-clicking it, and rerun yor procedure. It will "find" this module, but none of the others.

Again: Setting a reference to a module in the Modules collection requires that the module is open.

Hi Matthias,

I could not get away from the problem...

I did the following experiment:

Open "Archief" database - choose Modules - ontwerpen (modify?) Sub_Nieuwe_gebeurtenissen - close all modules one by one through red cross  - close "Archief" - run Show_problem.

The result is:

"Show problem"

10-okt-2015

Database	Module	Resultaat
Archief	A_opslaan	OK
Archief	Algemeen	OK
Archief	Knoppen	OK
Archief	Question	OK
Archief	Sub_Doc_relaties	OK
Archief	Sub_Gebeurtenissen	OK
Archief	Sub_Nieuwe_gebeurtenissen	Not found
Archief	Sub_Op_een_dag	OK
Archief	Sub_Ouders_def	OK
Archief	Sub_Overlijden	OK
Archief	Sub_Partners	OK
Archief	Sub_Pers_relatie	OK
Archief	Sub_Voorouder	OK
Archief	Tijdelijk	OK

Then the next experiment:

Open "Archief" database - choose Modules - ontwerpen (modify?) Sub_Nieuwe_gebeurtenissen - close "Archief" - run Show_problem.

The result is again the same:

"Show problem"

10-okt-2015

Database	Module	Resultaat
Archief	A_opslaan	OK
Archief	Algemeen	OK
Archief	Knoppen	OK
Archief	Question	OK
Archief	Sub_Doc_relaties	OK
Archief	Sub_Gebeurtenissen	OK
Archief	Sub_Nieuwe_gebeurtenissen	Not found
Archief	Sub_Op_een_dag	OK
Archief	Sub_Ouders_def	OK
Archief	Sub_Overlijden	OK
Archief	Sub_Partners	OK
Archief	Sub_Pers_relatie	OK
Archief	Sub_Voorouder	OK
Archief	Tijdelijk	OK

In this second experiment, when I open the module Sub_Nieuwe_gebeurtenissen, then all other modules are opened too.

So it seems that opening or closing modules has no effect in my environment.

I also did the experiment with an explicit opening of the module before referencing it. It gives me a 5 minute flickering show on the screen of opening all the modules in 90 applications, but indeed, all modules got an "OK".

What I still do not understand is that when setting a reference to a module in the Modules collection it REQUIRES that the module is open, why then can I reference most of the modules without explicit opening?

"There is still more in this Access-world that we don't understand"

Imb.

Answer 22

I'm not completely sure I understand all the circumstances of your tests, but it has been my experience that the open/closed state of modules in the VBA window is not changed unless a Save is executed in the VBA editor.  Those modules that are open in the VBA editor when a Save is performed will be opened automatically when the database is next opened.  Incidentally, just clicking the Save button won't necessarily execute a Save operation -- the save is only performed if a module is dirty.

So, if you have a bunch of modules saved in the open state, so that they are automatically opened when the database is opened, it doesn't matter if you open the database, close them all, and then click the save button.  If you then close the database and reopen it, thos same modules will be opened again automatically.  But if you then go into one of those modules, change something just to dirty the module, then close all the open modules and *then* click the save button, then if you close the database and reopen it, the modules will remain closed when the database reopens.

I see your description of this test:

Open "Archief" database - choose Modules - ontwerpen (modify?) Sub_Nieuwe_gebeurtenissen - close "Archief" - run Show_problem. The result is again the same

That seems to contradict my understanding of how this should work, but you don't mention whether, when you modified Sub_Nieuwe_gebeurtenissen, you closed the module window before exiting the database, or at what point the VBA Editor saved your changes.  Could it be that you modified the module and closed it, and only then clicked Save or closed the database and allowed it to prompt you to save the changes?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 23

I see your description of this test:

Open "Archief" database - choose Modules - ontwerpen (modify?) Sub_Nieuwe_gebeurtenissen - close "Archief" - run Show_problem. The result is again the same

Hi Dirk and Matthias,

Thank you, I now can reproduce the results, and then - all of a sudden - it looks quite simple.

When one of the modules is edited, you get the question to save the (changed) modules at closing the database. If you choose "yes", then the at that moment open modules are stored as open.

Both conditions must be fulfilled: a change and a save. In the above experiment I problably entered the modify mode, but did not modify a thing.

When a module is stored as open, it is not necessary to open the module explicitly with: cur_app.DoCmd.OpenModule mod_naam

As a workable solution to inspect all modules, open or closed, I will use the Pak_module function, extended with an explicit opening of the module if it is not open.

Imb.

Answer 24

As a workable solution to inspect all modules, open or closed, I will use the Pak_module function, extended with an explicit opening of the module if it is not open.

Hi,

And this is the result:

Function Pak_module(cur_app As Application, mod_naam As String, cur_module As Module) As Boolean

  On Error GoTo stop_it
  
opnieuw:
  Set cur_module = cur_app.Modules(mod_naam)
  Pak_module = True
  Exit Function
  
stop_it:
  cur_app.DoCmd.OpenModule mod_naam
  GoTo opnieuw
End Function

Imb.

Answer 25

Function Pak_module(cur_app As Application, mod_naam As String, cur_module As Module) As Boolean

  On Error GoTo stop_it
  
opnieuw:
  Set cur_module = cur_app.Modules(mod_naam)
  Pak_module = True
  Exit Function
  
stop_it:
  cur_app.DoCmd.OpenModule mod_naam
  GoTo opnieuw
End Function

I'd suggest that instead of a GoTo in your error-handler, you use the Resume statement. I don't know for sure what all goes on behind the scenes in VBA error-handling, but I have a feeling that un-Resumed errors may take a small bite out of stack space -- probably not important in most situations, but it's tidier to let a Resume statement clean up.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 26

I'd suggest that instead of a GoTo in your error-handler, you use the Resume statement. I don't know for sure what all goes on behind the scenes in VBA error-handling, but I have a feeling that un-Resumed errors may take a small bite out of stack space -- probably not important in most situations, but it's tidier to let a Resume statement clean up.

Thank you, Dirk,

Sometimes the process of typing goes faster than the process of thinking.

Imb.