Left Joins and record insert problems

[ckirby]

I have a situation where I'm trying to add a text field to allow
large Notes to be linked to record in an existing table (rather than
simply adding the new field into the table since relatively few
records will have the Note)

Tables are basically:

T1
ID InvNumFK
----------------------
40 | 1
42 | 2
43 | 2
44 | 1

T2
MatDetId EqpNote
-------------------------------------------------
40 | text
44 | additional Note

I'm trying to create a subform that will allow the user to view/update
records in T1 (there are other fields in T1 that aren't relevant to
the problem) and also add a record into the T2.EqpNote field or edit a
record that already exists.

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
--'master' table
CREATE TABLE [dbo].[T1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[InvNumFK] [int] NOT NULL

) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblBSMaterialDe t] WITH NOCHECK ADD
CONSTRAINT [PK_T1] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO

--Sub table
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[T2]') and OBJECTPROPERTY( id, N'IsUserTable') = 1)
drop table [dbo].[T2]
GO

CREATE TABLE [dbo].[T2] (
[MatDetId] [int] NOT NULL ,
[EqpNote] [text] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[T2] WITH NOCHECK ADD
CONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED
(
[MatDetId]
) ON [PRIMARY]

[Erland Sommarskog]

(ck****@mindspr ing.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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx