[AccessD] Circular reference error in query...

Bobby,

I'm nothing like an expert with SQL, so I probably shouldn't even hazard
a guess here, but I notice that you haven't explicitly stated the fields
being updated in the target table.  Assuming that this is generally
acceptable syntax, and that there's not some default behavior that
attempts to match data by field names (like I said, I'm no expert), your
query may be trying to push into the primary key field of the target
table the first field of the recordset returned by the select clause.
Since that field may contain duplicates, you'd get a key violation upon
execution when the first duplicate is encountered.

I'd try specifying the fields to insert into for the target table:

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;

Hope this helps . . .

Don

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bobby Heid
Sent: Thursday, April 06, 2006 9:09 AM
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