Posted by Erland Sommarskog on 04/14/06 01:17

(ckirby@mindspring.com) writes:
> This query works in Access (allows me to type in text into the EqpNote
> field and automatically create a new record in T2 with the T1.Id value
> in the T2.MatDetId field) :
> SELECT T1.ID, T1.InvNumFK, T2.EqpNote, T2.MatDetId
> FROM T1 LEFT JOIN T2 ON T1.ID = T2.MatDetId
> WHERE (((T1.ID)=10230));
>
> but when I try to 'translate' it into a version that I can use for the
> subform in the adp (it's an Access 2000 project with a SQL Server 2000
> backend) the 'child' record does not get added into T2.
>
> Here's the record source I'm using for the form in the adp
> SELECT T1.ID, T1. InvNumFK, T2.MatDetId, T2.EqpNote FROM T1 LEFT
> OUTER JOIN dbo.T2
> ON T1.ID =T2.MatDetId
> WHERE T1.InvNumFk = XX

This sounds like mumbo-jumbo to me. You have a SELECT query, and from
that you are trying to update a table? What about using INSERT
statements?

Since I don't know Access or ADP, I cannot be of much help. But I
have never liked client API that constructs code behind your back.
Sooner or later, they will outsmart you.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]