Microsoft KB Archive/169955

= ACC: Parameter Query Returns No Records with Replication ID Field =

Article ID: 169955

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q169955



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



SYMPTOMS
When you run a query based on a table that contains an AutoNumber field, and the field size of the AutoNumber field is set to Replication ID, no records are returned.



CAUSE
The query is using an implicit parameter for criteria on the AutoNumber field, and the AutoNumber field is not indexed.



RESOLUTION
There are two methods to work around this problem. You can create an index on the AutoNumber field in the table, or you can explicitly define the parameter within the query.

Method 1 - Creating an Index on the AutoNumber Field

 * 1) Open the table in Design view.
 * 2) Click in the row containing the AutoNumber field.
 * 3) Under Field Properties, click the General tab.
 * 4) Set the Indexed property of the AutoNumber field to either "Yes (Duplicates OK)" or "Yes (No Duplicates)".
 * 5) Save the table and close it.

Method 2 - Explicitly Defining the Parameter
 Open the query in Design view. On the Query menu, click Parameters.  In the Query Parameters dialog box, type the name of the parameter in the Parameter column, and then select Replication ID in the Data Type column. For example, if the parameter that the query is expecting is [Forms]![Form1]![Field1], then the Query Parameters dialog box should appear as follows:

     Parameter                   Data Type --     [Forms]![Form1]![Field1]    Replication ID                         Click OK to close the Query Parameters dialog box.

<div class="status_section">

STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 7.0 and 97.

<div class="moreinformation_section">

Steps to Reproduce Problem
 Open the sample database Northwind.mdb.</li>  Create the following new table:

<pre class="fixed_text">     Table: Table1 --     Field Name: Field1 Data Type: AutoNumber Field Size: Replication ID     Indexed: No

Field Name: Field2 Data Type: Text Field Size: 50 Indexed: No                       </li> Close and save the table. When you see a message asking if you want to create a primary key, click No.</li> Using the Autoform: Columnar Wizard, create a new form based on the Table1 table.</li> Type some text in the Field2 field so that a new Replication ID is entered into the first record.</li> On the Records menu, click Save Record to save the new record. Do not move the form to a new record.</li> Save the form as Form1. Do not close the form.</li>  Create the following new query based on the Table1 table, and open it in Design view:

<pre class="fixed_text">     Query: Query1 --     Type: Select Query

Field: Field1 Table: Table1 Criteria: [Forms]![Form1]![Field1]

Field: Field2 Table: Table1 </li> Run the query. Note that no records are returned, even though the form's current record contains a Replication ID that exists in the table.</li></ol>

<div class="references_section">