ACC: Parameter Query Returns No Records with Replication ID Field (169955)


The information in this article applies to:

  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
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


  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.

STATUS

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

MORE INFORMATION

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.

REFERENCES

For more information about Replication ID AutoNumbers, search the Help Index for "Replication IDs, AutoNumber fields" and then select "AutoNumber fields and replication."
Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kbbug KB169955
©2004 Microsoft Corporation. All rights reserved.