Hi All,

I use this query as a recordsource but it is not accepted:

strSource = "SELECT Tabel_Aanvragen.*, VISA.* " & _
"FROM Tabel_Aanvragen INNER JOIN VISA ON Tabel_Aanvragen.ID As ID
= VISA.ID ORDER BY Tabel_aanvragen.datum_registratie DESC"

Form_subFrmTabelTotaal.RecordSource = strSource


It has something to do with the ..... As ID.
Problem is I need it.
What is the problem?

Regards
Marco

Co wrote:
You cannot assign an alias in the ON clause. Change it to
ON Tabel_Aanvragen.ID = VISA.ID

Why do you think you need it in the ON clause?

If you need to assign an alias to a column, you need to do it in the
SELECT clause. However, the fact that you are assigning an alias that is
the same as the field's real name will likely cause a "circular
reference" error.

Also, you should really get rid of those wildcards and explicitly name
the fields you want to return in this query. For instance, given that
you have an inner join, there is absolutely no need to return the ID
from both tables since it is by necessity, the same value. If you really
need to return both for some reason, you need to differentiate them with
different aliases, like:

SELECT Tabel_Aanvragen.ID As IDAan, VISA.ID As IDVisa

I would also assign a shorter alias to the Tabel_Aanvragen table so you
don't have to type out that table name every time. Note, it is only
necessary to qualify the field names if the names are ambiguous, i.e.,
fields with the same name in both tables being joined.


--
HTH,
Bob Barrows

try it like this:

strSource = "SELECT t1.*, t2.* " _
& "FROM Tabel_Aanvragen t1 INNER JOIN VISA t2
ON t1.ID = t2.ID ORDER BY t1.datum_registratie DESC"

Rich

*** Sent via Developersdex http://www.developersdex.com ***

On 29 sep, 18:09, Rich P <rpng... (AT) aol (DOT) com> wrote:
The fact that I have the ID's from both tables is because when I set a
filter on the Tabel_aanvragen
the form looses the value for the ID, which I need to open the record
in a different form.

MyID = form.subform.ID

Marco

If you want to alias the field names you must list the field name in the
select clause and apply the alias for the field name there.

The following MIGHT WORK for you.

strSource = "SELECT Tabel_Aanvragen.*, VISA.*, Tabel_Aanvragen.ID as ID " & _
"FROM Tabel_Aanvragen INNER JOIN VISA ON Tabel_Aanvragen.ID As ID
= VISA.ID ORDER BY Tabel_aanvragen.datum_registratie DESC"

I would be more comfortable using an alias like MyID, THeID or even A_ID.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Co wrote: