Insert Error 30014

[Guest]

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.'

 

[Guest]

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!

 

[Sylvain Lafontaine]

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/micro...rt+trigger+@@identity&rnum=1#2f5ce86f0fe13b0a .

 

[Guest]

That makes perfect sense! Thank you again for all your help.
RJ

 

[JJ]

Sorry, just a test !

That makes perfect sense! Thank you again for all your help.
RJ