[AccessD] Circular reference error in query...

Very odd, and I can't explain it. But that never stopped me before. :-)

First I'd get rid of the Ordered By clause. It makes no sense to sort
records while you are appending them to a table, because the order of
records in a table is simple not defined. All sorting should be done by
queries when you select data to view it.

Second, the only thing I can think of is to explicitly identify the source
table of the ID field in the WHERE cause. So try this SQL instead...

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

HTH

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bobby Heid
Sent: Thursday, April 06, 2006 12:09 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Circular reference error in query...


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