Hi,
Sorry, the post a new thread button is not working, so I can only reply to
the message in order to post my question.
I have two tables: tbl_A, tbl_B and a query qry_C basically just combine
some of the fields plus do some simple calculation for both tables. Now I
want to insert a lookup column in tbl_A to view some data from qry_C. From
lookup wizard, it shows “No valid fields can be found in “qry_C”. you may
have selected a query that uses the table you’re adding the lookup column to.
Please select a new source”. Does anyone can tell me that if this error is
normal for my particular situation? Or how can I avoid it?
You can avoid it by... not using the very limited, misleading, and
generally disliked Lookup Wizard. See
http://www.mvps.org/access/lookupfields.htm
for a critique.
The specific cause of the error message is exactly what it says:
you're defining a lookup field in tbl_A which contains a circular
reference to tbl_A. The wizard isn't smart enough to figure this out!
Instead, create a Combo Box on the FORM that you should consider using
to interface with tbl_A. You can base this Combo Box ("lookup" if you
will) on qry_C. Or, since you're presumably looking up a calculated
value, consider doing the calculation right in the Control Source of
the Form (perhaps using DLookUp() to look up a value from tbl_B).
Perhaps you could post the SQL view of qry_C and indicate what you're
trying to look up!
John W. Vinson[MVP]
