multi part identifier could not be bound

[Keith G Hicks]

This has me stumped. I have 2 tables involved in a query (first one shown
below) as the record source for a subform. When I run this query in SSMS it
runs fine. When I open the subform on it's own it runs fine. But when I open
the main form (which has a parent table as its source), I get the "multi
part identifier could not be bound" error. The link child and link master
field properties are set to AdjLetterSchedID. I have a second subform for
the same main form that uses antoher simpler query with the same
master/child linking and it runs okay as well in all cases.

Query for subform that doesn't work:

SELECT tAS.AdjLetterSchedID, tA.AttyID, tA.AttyOfficeName FROM
dbo.tblAdjLetterSched_AttyIDs tAS INNER JOIN dbo.tblAttorneys tA ON
(tAS.AttyID = tA.AttyID) ORDER BY tA.AttyOfficeName


Query for subform that does work:

SELECT AdjLetterSchedID, County FROM dbo.tblAdjLetterSched_Counties ORDER BY
County


I'll give more info if needed but I'm hoping this will ring a bell for
someone.

Any ideas?

Keith

 

[Sylvain Lafontaine]

With ADP, link child and master fields properties don't play well with
complex queries; only with Stored Procedures and queries based on single
table; if I remember correctly.

This is because ADP has to rewrite your query as a subquery in order to
filter out the rows not covered by the link fields. If you take a look with
the SQL-Server Profiler, you'll see what I'm talking about.

You can try adding an explicit alias such as "SELECT tAS.AdjLetterSchedID as
AdjLetterSchedID, ..." but I don't remember if this will work or not.

Personally, I always use stored procedures or EXEC sql strings when working
with subforms and also I've always specify the UniqueTable and the
ResyncCommand properties; otherwise, it doesn't take to much to have binding
errors or to get a read-only form or subform.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Keith G Hicks]

Okay. Thanks Sylvain. That helps.
And I did try aliasing the column names a little while ago. You're right.
That doesn't help.