Microsoft KB Archive/824180

= Compilation is not successful when you convert a macro to a module in Microsoft Access =

Article ID: 824180

Article Last Modified on 4/12/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file.



SYMPTOMS
In Microsoft Access, 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 &quot;Symptoms&quot; 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 &quot;More Information&quot; section of this article with the following code: Function Macro1_TestMacro On Error GoTo Macro1_TestMacro_Err

If (Forms![Orders Subform]!Product = &quot;Pavlova&quot;) 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



Steps to Reproduce the Behavior
 Open the Northwind sample database in Access. In the Database window, click Macros under Objects, and then click New.

Note In Access 2007, click Macro in the Other group on the Create tab. On the View menu, click Macro Names, and then click Conditions.

Note In Access 2007, make sure that Macro Names, Show All Actions and Conditions are selected in the Show/Hide group on the Design tab. In the Macro Name column, type TestMacro . In the Condition column, type the following:

[Forms]![Orders Subform]![Product]=&quot;Pavlova&quot;

</li> In the Action column, select SetValue from the list.</li> In the Action Arguments pane, set Expression to Yes, and then set Item to the following:

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

</li> On the File menu, click Save As.

Note In Access 2007, click Microsoft Office Button, and then click Save As.</li> In the Save as dialog box, click OK.</li> On the File menu, click Save As.

Note In Access 2007, click Microsoft Office Button, and then click Save As.</li> In the Save as dialog box, select Module from the As list box, and then click OK.</li> In the Convert macro: Macro1 dialog box, click Convert.</li> In the Conversion Finished message box, click OK.</li> In the Database window, click Modules under Objects.

Note In Access 2007, click the Module group in the left navigation pane.</li> Double-click Converted Macro-Macro1 to open the macro.</li></ol>

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          = &quot;Pavlova&quot;) 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

Additional query words: ACC2007

Keywords: kbexpertiseinter kbtshoot kbprogramming kbmacro kbprb KB824180

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.