If I create sub-form using a join query or Stored procedure, MSDE shows strange errors when I try to insert a record in subform. The following example for reference

Table Name : TestTable1

EID_1 INT PRIMARY KEY IDENTITY 1
FULLNAME VARCHAR(40)
ETypeID INT INDEXED

Table Name : TestTable2

EID_2 PRIMARY KEY
FirstName VARCHAR(40)
LastName VARCHAR(40)

Table Name : TestTable3

EID_3 PRIMARY KEY
Address1 VARCHAR(40)
Address2 VARCHAR(40)

RELATIONSHIP (TestTable1.EID_1 = TestTable2.EID_2)
X Columns related Enforce relationship for replication
X Enforce relationships for INSERTs and UPDATEs
X Cascade Delete related records
RELATIONSHIP (TestTable1.EID_1 = TestTable3.EID_3)
X Columns related Enforce relationship for replication
X Enforce relationships for INSERTs and UPDATEs
X Cascade Delete related records

Query : TestQuery2 - Join
SELECT TestTable2.EID_2,
TestTable2.FirstName,
TestTable2.LastName,
TestTable3.EID_3,
TestTable3.Address1,
TestTable3.Address2
FROM TestTable2
LEFT OUTER JOIN TestTable3
ON TestTable2.EID_2 = dbo.TestTable3.EID_3

Form : TestForm1
RecordSource : TestTable1
Unique Table : TestTable1

Sub Form: TestForm2
RecordSource : TestQuery1
Unique Table : TestTable1

Can anybody help to fix , is it bug? I cant compromise with database design because it is upsizing project from Access. What are the possible workarrounds for this ?


Another bug I found
if we creat a subform with storedprocedure as its record source. Subform creating Wizard shows different datatypes (I used INT datatype and wizard showed it as as REAL) thus, it makes impossible to synchronise the main form with subform