[AccessD] Circular reference error in query...

this is a shot in the dark but maybe because destination columns are not 
defined in the insert statement, values are being inserted into autonumber 
field thereby producnig duplicate pk values.  perhaps defining destination 
columns may get rid of the error message.. that is,

INSERT INTO 
wrkSearchAvailable(FieldID,FieldDescr,DBField,UnionField,ColWidth,ColVisible,UserID)
	SELECT FieldID,FieldDescr,DBField,UnionField,ColWidth, _
		ColVisible,UserID
	   FROM wrkSearchSelected
	      WHERE ID IN (11)
		ORDER BY FieldDescr ASC;

HTH
Billy


>From: "Bobby Heid" <bheid at appdevgrp.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: [AccessD] Circular reference error in query...
>Date: Thu, 6 Apr 2006 12:09:05 -0400
>
>Hey,
>
>I have the following:
>
>wrkSearchAvailable and wrkSearchSelected tables (defined the same):
>ID 		- autonumber/primary key
>FieldID	- long/indexed (dupes ok)
>FieldDescr	- text(255)/not indexed
>DBField	- text(255)/not indexed
>UnionField	- text(50)/not indexed
>ColWidth	- double/not indexed
>ColVisible	- boolean/indexed (dupes ok)
>UserID	- text(75)/indexed (dupes ok)
>
>
>Still here?  Good.
>
>My query is built in VBA and the 11 in the 'IN (11)' is from a listbox -
>there's only one in this example.
>
>Here is the query:
>INSERT INTO wrkSearchAvailable
>	SELECT FieldID,FieldDescr,DBField,UnionField,ColWidth, _
>		ColVisible,UserID
>	   FROM wrkSearchSelected
>	      WHERE ID IN (11)
>		ORDER BY FieldDescr ASC;
>
>This query copies the selected records from wrkSearchSelected to
>wrkSearchAvailable.  The problem is that when this runs, I am getting this
>error:
>
>error 3022
>'The changes you requested to the table were not successful because they
>would create duplicate values in the index, primary key, or relationship.
>Change the data in the field or fields that contain duplicate data, remove
>the index, or redefine the index to permit duplicate entries and try 
>again.'
>
>If I paste the SQL into the QBE, and try to execute it by clicking on the
>datasheet button, I get this error message:
>"Circular reference caused by alias 'FieldID' in query definition's SELECT
>list."
>
>If I execute the query using the Run button (!), I get a message that it
>could not update because of key violations.
>
>I appreciate that you have made it this far.  Anyone have any ideas as to
>why I am having this issue?
>
>Thanks,
>Bobby
>
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com