Compilation is not successful when you convert a macro to a module (824180)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
Moderate: Requires basic macro, coding, and interoperability skills. This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you convert a macro to a module, and you then try to compile the module, the compilation may not be successful. You may see this problem only when the macro contains a condition and when that condition statement refers to a form or to any database object.

CAUSE

This problem occurs when the condition that is specified in the macro refers to a form or to a database object and the name of the object contains spaces. The spaces in the name of the object introduce an extra line when the macro is converted to a module. Because of this, the compilation of the module is not successful.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

WORKAROUND

To work around the problem that is described in the "Symptoms" section, open the module that is created when the macro is converted to a module, and then delete the extra space or line from the name of database object. Replace the code sample that is provided in "More Information" section of this article with the following code:
Function Macro1_TestMacro()
On Error GoTo Macro1_TestMacro_Err

    If (Forms![Orders Subform]!Product = "Pavlova") Then
        Forms![Orders Subform]!Product.Enabled = True
    End If


Macro1_TestMacro_Exit:
    Exit Function

Macro1_TestMacro_Err:
    MsgBox Error$
    Resume Macro1_TestMacro_Exit

End Function

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open the Northwind.mdb sample database.
  2. In the Database window, click Macros under Objects.
  3. Click New.
  4. On the View menu, click Macro Names.
  5. In the Macro Name column, type TestMacro.
  6. On the View menu, click Conditions.
  7. In the Condition column, type the following:

    [Forms]![Orders Subform]![Product]="Pavlova"

  8. In the Action column, select SetValue from the list.
  9. In the Action Arguments pane, set Item to the following:

    [Forms]![Orders Subform]![Product].[Enabled]

  10. In the Action Arguments pane, set Expression to Yes.
  11. On the File menu, click Save As.
  12. In the Save as dialog box, click OK.
  13. Close the macro.
  14. Right-click the Macro1 macro.
  15. Click Save as.
  16. In the Save as dialog box, select Module from the As list box, and then click OK.
  17. In the Convert macro: Macro1 dialog box, click Convert.
  18. In the Conversion Finished message box, click OK.
  19. In the Database window, click Modules under Objects.
  20. Double-click Converted Macro-Macro1 to open the macro.
After you run the code, the code is modified as follows, and the debugger shows the error in the If statement in red: 
Function Macro1_TestMacro()
On Error GoTo Macro1_TestMacro_Err

    If (Forms![Orders Subform]!Product
                                                                                                                                                                                                                                                               
           = "Pavlova") Then
        Forms![Orders               Subform]!Product.Enabled = True
    End If


Macro1_TestMacro_Exit:
    Exit Function

Macro1_TestMacro_Err:
    MsgBox Error$
    Resume Macro1_TestMacro_Exit

End Function

REFERENCES

For more information about macros, click Microsoft Office Access Help on the Help menu, type macro in the Search for box in the Assistance pane, and then click Start searching to view the topic.
Modification Type:MinorLast Reviewed:6/8/2004
Keywords:kbProgramming kbmacro kbprb KB824180 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.