Odd subform error

[Maury Markowitz]

I have a read-only subform that collects data from one unique table,
and another "data table". Here is the SQL in the recordSource:

SELECT q.*, CASE WHEN b.VendorShortName IS NOT NULL THEN
b.VendorShortName ELSE b.VendorName END AS brokerName
FROM tblQuotes q INNER JOIN tblBrokers b ON b.ID = q.brokerId ORDER
BY q.[date] DESC

The query works as expected, returning 33 rows when there's no filter.
When I open the subform itself everything displays properly, with 33
rows. However, when I bind this into a form and join on accountId (so
the user can select a bank account and see what's there) I get an
error:

The column prefix 'q' does not match with a table name or alias name
used in the query.

Obviously 'q' IS being used as an alias name, and one of it's columns
is accountId. What does this error actually mean?

Maury

 

[Ken Snell MVP]

Are you putting [q].[accountId] as the LinkChildFields property? If yes,
change it to just [accountId].

I'm intrigued by your use of CASE syntax in an ACCESS query. I am not aware
that this can be used in ACCESS SQL; can you elaborate on how you are able
to use that syntax in ACCESS?

 

[Dirk Goldgar]

Are you putting [q].[accountId] as the LinkChildFields property? If yes,
change it to just [accountId].

I'm intrigued by your use of CASE syntax in an ACCESS query. I am not
aware that this can be used in ACCESS SQL; can you elaborate on how you
are able to use that syntax in ACCESS?

Maury works with ADPs.

 

[Ken Snell MVP]

Are you putting [q].[accountId] as the LinkChildFields property? If yes,
change it to just [accountId].

I'm intrigued by your use of CASE syntax in an ACCESS query. I am not
aware that this can be used in ACCESS SQL; can you elaborate on how you
are able to use that syntax in ACCESS?

Maury works with ADPs.

Aha, that answers the question.

 

[Maury Markowitz]

Are you putting [q].[accountId] as the LinkChildFields property? If yes,
change it to just [accountId].

No. I tried this but then the subform complained and wouldn't open. I
removed it and got the original error again.

I assume this is occurring because of the re-writing that happens when
the subform's WHERE is constructed. I don't know how to debug this
though, is there a way to capture the SQL that Access is sending? Log
files in SQL2000 are not at all easy to use for this.

Maury

 

[Maury Markowitz]

Ok, some further info... if I remove the join to the read-only table,
the problem disappears. Not that that's useful...

 

[Ken Snell MVP]

I'm afraid I don't have hands-on knowledge with ADP databases, so I must
respectfully step aside and let someone with such knowledge post a reply for
you. Sorry.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Are you putting [q].[accountId] as the LinkChildFields property? If yes,
change it to just [accountId].

No. I tried this but then the subform complained and wouldn't open. I
removed it and got the original error again.

I assume this is occurring because of the re-writing that happens when
the subform's WHERE is constructed. I don't know how to debug this
though, is there a way to capture the SQL that Access is sending? Log
files in SQL2000 are not at all easy to use for this.

Maury

 

[Dirk Goldgar]

Ok, some further info... if I remove the join to the read-only table,
the problem disappears. Not that that's useful...

I don't have any experience with ADPs, but maybe if you posted the SQL of
the joined query that is causing the problem, we could see something what's
wrong.

 

[Maury Markowitz]

experience with ADPs, but maybe if you posted the SQL of
the joined query that is causing the problem, we could see something what's
wrong.

Posted in full above. As I noted, the SQL works fine, and the form
works fine, UNTIL you put it in a subform. Then you get the error.

Maury

 

[Dirk Goldgar]

experience with ADPs, but maybe if you posted the SQL of
the joined query that is causing the problem, we could see something
what's
wrong.

Posted in full above. As I noted, the SQL works fine, and the form
works fine, UNTIL you put it in a subform. Then you get the error.

Then I don't understand what you mean when you refer to "join[ing] to the
read-only table". Please explain. (However, I have to leave my computer
now, so I won't be able to reply until later, maybe not until Sunday.)