Access subforms linked to SQL Tables Show Inserted records as deleted

[BOSSupport]

After upsizing my Access 2003 application to SQL Server I am noticing some strange behavior in sub forms. I can insert a record but as soon as I save it the record shows #deleted. If I requery the subform the record returns. The Primar Key is an autonumber being managed by the MS created Insert Trigger. I think that that is the problem but don't know how to approach it. This must have been encountered and a solution discovered.

Here is the supplied Trigger:

/****** Object: Trigger dbo.T_tblChildren_ITrig Script Date: 7/16/2005 10:22:07 AM ******/
CREATE TRIGGER T_tblChildren_ITrig ON [tblChildren] FOR INSERT AS
SET NOCOUNT ON
DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'ChildPrimaryID' */
SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @newc = (SELECT ChildPrimaryID FROM inserted)
UPDATE tblChildren SET ChildPrimaryID = @randc WHERE ChildPrimaryID = @newc

GO

I would appreciate any help that anyone could provide. Thanks in advance.

 

[jsteph]

You're creating a child record and altering the key behind access' back, it's looking like you'll need to automatically requery the subform.

You could put the me.requery in the AFterInsert of the subform, this should do it.
--Jim