Article ID: 167066
Article Last Modified on 1/19/2007
APPLIES TO
- Microsoft Access 97 Standard Edition
This article was previously published under Q167066
Moderate: Requires basic macro, coding, and interoperability skills.
SYMPTOMS
When you convert a macro to Visual Basic for Applications code using the "Save as Visual Basic Module" command, and the macro contains a RunSQL action that includes a SQL Statement that was copied from a query's SQL View window, you may receive a compile error when you try to compile the module.
CAUSE
The RunSQL Statement is not being correctly copied to the module during the conversion and is causing the module to fail.
RESOLUTION
There are two ways to resolve this problem:
- Edit the SQL Statement Action Argument in the macro and remove the space character to the left and right of the equal sign (=). Save, close, and convert the macro again.
- Edit the code in the converted macro module, and remove the extra line.
STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 97.
MORE INFORMATION
Steps to Reproduce Problem
- Open the Northwind.mdb database in Microsoft Access 97 and create a new query in Design view.
- Add the Employees table to the query grid, and on the Query menu, click Update Query.
- In the Field row, select ReportsTo.
- In the Update To row, type 1.
- On the View menu, click SQL View and copy the SQL Statement from the SQL View window. Close the query.
- Create a new macro and add the RunSQL action to the macro. Paste the SQL syntax from the query in the SQL Statement box. Close and save the macro as Macro1.
- Run the Macro1 macro.
- On the File menu, click Save As/Export. In the Save As box, click "Save as Visual Basic Module," and then click OK.
- In the Convert Macro: Macro1 box, click Convert.
- Click OK in the "Macro to VBA Conversion" message box.
- Open the converted macro module in Design view. Note that the RunSQL Statement has been incorrectly divided into two lines of code.
- On the Debug menu, click Compile Loaded Modules. Note that you receive a compile error.
REFERENCES
For more information about using the "Macro to Visual Basic" command, search the Help Index for "converting macros," and then "converting macros to Visual Basic," or ask the Microsoft Access 97 Office Assistant.
Keywords: kbbug kbprogramming KB167066
