Microsoft KB Archive/287487

Knowledge Base

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.

MORE INFORMATION

Steps to Reproduce the Behavior

1. Start Microsoft Access, and then create a new, blank database.
2. 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.

3. 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]
                       

4. Open the Customers form, and then select any record.
5. Open the Test1 form and view the data displayed in the combo box.
6. Close both forms.
7. On the Tools menu, point to Database Utilities, and then click Upsizing Wizard.
8. On the first page of the wizard, click Create new database, and then click Next.
9. Specify a valid SQL server name, logon credentials, and a database name. Click Next.
10. Select to upsize the Customers table, and then click Next.
11. Accept the default options, and then click Next.
12. Click the Create a new Access client/server application option. In the ADP File Name box, enter C:\AccessCS.adp. Click Next.
13. Click Open the New ADP File, and then click Finish.
14. In the new ADP file, open the Customers form, and then select any record.
15. Open the Test1 form.

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

REFERENCES

For additional information about implementing query by form in Access projects, click the following article number to view the article in the Microsoft Knowledge Base:

286828 How to implement Query By Form (QBF) in an Access project

For additional information about parameterized combo boxes in Access projects, click the article number below to view the article in the Microsoft Knowledge Base:

281870 ACC2002: How to Use Parameterized Combo Boxes in an Access Project

For more information about named parameters and how to create them, refer to SQL Server Books Online at the following Microsoft Web site:

http://msdn.microsoft.com/library/psdk/sql/getstart_4fht.htm

Additional query words: pra

Keywords: kberrmsg kbbug KB287487

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.