Odd subform error

  • Thread starter Thread starter Maury Markowitz
  • Start date Start date
M

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
 
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?
 
Ken Snell MVP said:
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?
Click to expand...


Maury works with ADPs.
 
Dirk Goldgar said:
Ken Snell MVP said:
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?
Click to expand...


Maury works with ADPs.
Click to expand...

Aha, that answers the question.
 
Are you putting [q].[accountId] as the LinkChildFields property? If yes,
change it to just [accountId].
Click to expand...

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
 
Ok, some further info... if I remove the join to the read-only table,
the problem disappears. Not that that's useful...
 
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].
Click to expand...

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 said:
Ok, some further info... if I remove the join to the read-only table,
the problem disappears. Not that that's useful...
Click to expand...


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.
 
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.
Click to expand...

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
 
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.
Click to expand...


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.)
 
Back
Top