|
|
Posted by ckirby on 04/13/06 20:12
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]
Navigation:
[Reply to this message]
|