Microsoft KB Archive/169955: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
Line 74: | Line 74: | ||
# Click in the row containing the AutoNumber field. | # Click in the row containing the AutoNumber field. | ||
# Under Field Properties, click the General tab. | # Under Field Properties, click the General tab. | ||
# Set the Indexed property of the AutoNumber field to either | # Set the Indexed property of the AutoNumber field to either "Yes (Duplicates OK)" or "Yes (No Duplicates)". | ||
# Save the table and close it. | # Save the table and close it. | ||
Line 151: | Line 151: | ||
== REFERENCES == | == REFERENCES == | ||
For more information about Replication ID AutoNumbers, search the Help Index for | For more information about Replication ID AutoNumbers, search the Help Index for "Replication IDs, AutoNumber fields" and then select "AutoNumber fields and replication." | ||
</div> | </div> |
Latest revision as of 11:04, 21 July 2020
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
- Open the table in Design view.
- Click in the row containing the AutoNumber field.
- Under Field Properties, click the General tab.
- Set the Indexed property of the AutoNumber field to either "Yes (Duplicates OK)" or "Yes (No Duplicates)".
- 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.
STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 7.0 and 97.
MORE INFORMATION
Steps to Reproduce Problem
- Open the sample database Northwind.mdb.
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
- Close and save the table. When you see a message asking if you want to create a primary key, click No.
- Using the Autoform: Columnar Wizard, create a new form based on the Table1 table.
- Type some text in the Field2 field so that a new Replication ID is entered into the first record.
- On the Records menu, click Save Record to save the new record. Do not move the form to a new record.
- Save the form as Form1. Do not close the form.
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
- 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: kbbug KB169955