Left Joins and record insert problem

[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].[tblBSMaterialDet] 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_General_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]

[Rich P]

My hunch is that your problem is with the text field. Sql server is
very touchy with text fields. I do know that the data in a text field
does not reside in the table that the text field is defined in.

If it is possible in your design, you might try changing the field to
nvarchar(8000) if that is enough chars for your needs.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Razvan Socol]

Hello,

This question is more about Access ADP-s than about SQL Server. The
easiest way to solve this is to use another subform for the T2 table
(linked to the parent form by the MatDetId column, using
LinkChildFields/LinkMasterFields), so that each form is bound to
exactly one table. As long as you are not using datasheets (and since
we are talking about large notes, I suppose that datasheets are out of
the question), you can use a subform with a transparent border so the
user won't even notice that there is a subform.

Razvan