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.
↑ Back to the top
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.
↑ Back to the top
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.
↑ Back to the top
Microsoft
has confirmed that this is a problem in the Microsoft products that are listed
at the beginning of this article.
↑ Back to the top
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
↑ Back to the top
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]="Pavlova"
- In the Action column, select SetValue
from the list.
- In the Action Arguments pane, set Expression to Yes, and then set Item to
the following:
[Forms]![Orders
Subform]![Product].[Enabled]
- On the File menu, click Save
As.
Note In Access 2007, click Microsoft Office Button, and then click Save As. - In the Save as dialog box, click
OK.
- On the File menu, click Save
As.
Note In Access 2007, click Microsoft Office Button, and then click Save As. - In the Save as dialog box, select
Module from the As list box, and then click
OK.
- In the Convert macro: Macro1 dialog box,
click Convert.
- In the Conversion Finished message box,
click OK.
- In the Database window, click Modules
under Objects.
Note In Access 2007, click the Module group in the left navigation pane. - 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
↑ Back to the top