ACC: Parameter Query Returns No Records w/ Replication ID Field

• Microsoft Access versions 7.0, 97

SYMPTOMS

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.

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

Keywords : QryParm
Version : 7.0 97
Platform : WINDOWS
Issue type : kbbug