Trigger Causes Resync Problems

[Randal Greene]

We're building an Access 2000 ADP that connects to SQLServer 2000. We've
built a bound form (based on a single table) with a bound subform (based on
a single table) to handle a One-Many relationship and all worked as
expected, until we added an insert trigger on the Many table. The trigger
inserts records in a Third table that is not referenced on the form. Now
the Access subform gives the error "the data was added to the db but won't
be displayed in the form because it doesn't satisfy the record source
criteria". I found a number of threads and KB articles that seem
semi-related, but none of the fixes worked in our case:
- Set NoCount On in the trigger.
- Set the UniqueTable and ResyncCommand properties on the form.
- Use the Form_Error event to bury the error and requey the table.

Any other clues? Thanks in advance...

[Alden Raymundo]

Randal,

This is mostly a hunch, but does your trigger use the @@identity global
value? If so, your trigger may be 'running interference' on what Access is
trying to do behind the scenes on your many table. Basically, when you
insert a record into a form, behind the scenes the following is happening
on the server:

Insert into FOO

Select @@Identity

Select * from FOO where ID = @@Identity

This approach is used by Access to verify if the insert record worked and
also to return the value of the ID to "clean up" the display to replace
"auto
number" with the ID of the newly inserted record.

Since @@Identity is a global value across all tables, the value returned to
Access is the value from the *second* insert called from your trigger and
is
therefore the wrong value. Access shows this as an error.

HTH,

Alden Raymundo
Microsoft Access Developer Support

This posting is provided “AS IS” with no warranties, and confers no rights.

[Nik Sestrin]

problem solving - generate @@identity after *second* insert in trigger. e.g.

create trigger mytable_insert_trigger on mytable for insert as
declare @identity int, @strsql varchar(128)
set @identity=@@identity
--your code
--insert into secondtable ...
--your code
set @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ',
1) as id into #tmp'
execute (@strsql)

--
WBR
Nik Sestrin, www.softaura.com

"Alden Raymundo" <alden-...@microsoft.com> wrote in message
news:nke6El0fBHA.1960@cpmsftngxa09...

[Randal Greene]

That's it - thank you both...

I'm new SQLServer and TransactSQL - what's the story on #tmp?

RG

"Nik Sestrin" <ses...@astfnet.kuzbass.net> wrote in message
news:uChdfBOgBHA.2280@tkmsftngp03...

[eboo...@gmail.com]

Pure gold, thank you!!