Microsoft KB Archive/287487

= ACC2002: Upsizing Wizard Fails to Upsize Form References in WHERE Clause =

Article ID: 287487

Article Last Modified on 6/7/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q287487



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

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
To limit the number of records returned in a form, a combo box, or a list box, it is common practice to use a form reference. However, during the upsizing process, the Upsizing Wizard removes the form reference, leaving an incomplete SQL statement. When you run the incomplete SQL statement, you receive the following error message:

Invalid SQL Statement. Check the server filter on the form record source



CAUSE
The wizard fails to upsize the form reference in a combo box, a list box, or a form's record source. As a result, the SQL statement is incomplete. It is important to note that, when a query is upsized, the form reference is changed to a named parameter.



RESOLUTION
After the upsizing is finished, modify the SQL statement to include the correct named parameter instead of a form reference.



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



Steps to Reproduce the Behavior
 Start Microsoft Access, and then create a new, blank database. On the File menu, point to Get External Data, and then click Import. Import the Customers table and the Customers form from the sample database Northwind.mdb.  Create an unbound form in Design view with the following objects and properties:   Form: Test1 -  Caption: TestForm RecordSource:

Combo box ---  Name: Field1 RowSource: Select Customers.City FROM Customers WHERE (((Customers.City) = Forms!Customers![city]  Open the Customers form, and then select any record.</li> Open the Test1 form and view the data displayed in the combo box.</li> Close both forms.</li> On the Tools menu, point to Database Utilities, and then click Upsizing Wizard.</li> On the first page of the wizard, click Create new database, and then click Next.</li> Specify a valid SQL server name, logon credentials, and a database name. Click Next.</li> Select to upsize the Customers table, and then click Next.</li> Accept the default options, and then click Next.</li> Click the Create a new Access client/server application option. In the ADP File Name box, enter C:\AccessCS.adp. Click Next.</li> Click Open the New ADP File, and then click Finish.</li> In the new ADP file, open the Customers form, and then select any record.</li> Open the Test1 form.</li></ol>

Note that you receive the error message that is mentioned in the &quot;Symptoms&quot; section of this article.

<div class="references_section">