Re: Insert Error 30014

This problem is caused by the fact that the insert trigger change the value
of @@identity before the stored procedure finish: the value of @@identity
returned to ADP is not the right one. The usual solution is to store the
value of @@identity in a local variable at the beginning of the insert
trigger and reset the value of @@identity to this stored value at the end of
the trigger. For an exemple of code, see:

http://groups.google.ca/group/microsoft.public.access.adp.sqlserver/browse_frm/thread/a40e0e74406726a7/2f5ce86f0fe13b0a?lnk=gst&q=insert+trigger+%40%40identity&rnum=1#2f5ce86f0fe13b0a .

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"RJ" <RJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7EA106E1-BB02-4B8B-81E6-896F714D2C1B@xxxxxxxxxxxxxxxx
NOW I am really confused...

I found this spipet online

"To avoid the error i have to delete the trigger on the table witch create
a
new record in an other table"

And Yes I do have a database trigger for the Insert / Update Event on the
form's main table. I removed the trigger and the code worked fine.

HOWEVER I need that trigger to perform additional db related tasks!
Certainly code that does not belong in the fron end. So now what Mr./Ms
Wizard?

I do a have form_error event but that is not firing. Essentially I am now
totally confused about how to circumvent the error which in reality is not
an
error at all! I just need it to process as if the trigger were not there!

"RJ" wrote:

ADP 2003 - SQL Server 2000

Catch 22?

I have a form where I want to have underlying record source of a single
record. The stored procedure used for the record source is expecting an
EventId (which is an Identity Seed re: Autonum column). This works great
for
existing records.

The problem comes into play for new records. The EventId passed to the
stored procedure is 0 (indicating a new record) and once the user clicks
on a
"Save" button, I perform the DoCmd.RunCommand acCmdSaveRecord command.

This causes a 30014 error which states the record is added but does not
qualify for the underlying record source. I think the problem here is
that
the record source is for EventId 0 and the autonumm has just been
assigned
by SQL Server. After an add I change the record source to the new
EventId in
the AfterUpdate Event.

If I ignore the error or trap the error I do not have access (pardon the
pun) to the generated EventId in order to reset my recordsource. Also the
error causes the AfterUpdate event NOT to fire.

I swear that I do the exact same thing in another adp, successfully I
might
add but I cannot find a difference!

Anyone have any experience with this 30014 error? Any clue as to what I
may
be doing wrong?


30014 - 'The data was added to the database but the data won't be
displayed in the form because it doesn't satisfy the criteria in the
underlying record source.'


.

Loading