Microsoft KB Archive/169955

From BetaArchive Wiki

Article ID: 169955

Article Last Modified on 1/19/2007


  • 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.


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.


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


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

  1. Open the query in Design view.
  2. On the Query menu, click Parameters.
  3. 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
  4. Click OK to close the Query Parameters dialog box.


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


Steps to Reproduce Problem

  1. Open the sample database Northwind.mdb.
  2. Create the following new table:

          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
  3. Close and save the table. When you see a message asking if you want to create a primary key, click No.
  4. Using the Autoform: Columnar Wizard, create a new form based on the Table1 table.
  5. Type some text in the Field2 field so that a new Replication ID is entered into the first record.
  6. On the Records menu, click Save Record to save the new record. Do not move the form to a new record.
  7. Save the form as Form1. Do not close the form.
  8. Create the following new query based on the Table1 table, and open it in Design view:

          Query: Query1
          Type: Select Query
          Field: Field1
          Table: Table1
          Criteria: [Forms]![Form1]![Field1]
          Field: Field2
          Table: Table1
  9. 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.


For more information about Replication ID AutoNumbers, search the Help Index for "Replication IDs, AutoNumber fields" and then select "AutoNumber fields and replication."

Keywords: kbbug KB169955