Re: write conflict error

Thanks Crystal,

I was hoping to avoid adding the code to all of the forms that use that
table as the record source by using the trigger on the table. That seemed
the most effecient way to do it. But obviously that isn't going to work.
The trigger works fine without errors when I append data into that table
using a stored procedure (append query). The correct data goes into the
other two tables and no errors are recieved which makes me think that it is
something that is happening in Access when interfaced with SQL database.

Thanks for the reminder on adding data directly to the tables but the only
reason I was doing that was for testing purposes. I wanted to see if the
error was caused by the form or the trigger on the table. Thanks for your
time and efforts.

Cheers,

Ernie

"strive4peace" wrote:

Hi Ernie,

instead of executing on the SQL side, put the SQL statements
in the code behind the form. You shouldn't be adding data
to tables directly anyway ;)

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


Ernie wrote:
Hi Crystal,

This code is launched automatically by the SQL server when a record is
inserted into that table, hence the name "Update Trigger" and why I can get
the error when I update directly to the table without using a form. To get
to the code I right click on the table name in the Access window that
displays all of the tables and then I select triggers. I am new to using
access with SQL server and never had these problems with Jet db. ;-( Maybe
I cannot use triggers with Access?? Perhaps I have to use a stored procedure
and call it from an event in the form?? Although that seems like alot of
extra work if a trigger will do the same thing. I dunno!!

"strive4peace" wrote:


Hi Ernie,

The place for the save code to go is in the routine with
your SQL statements. I guess you are calling this your
Update Trigger ... how is it being launched? Command
button? Event procedure?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


Ernie wrote:

Thanks for your reply Crystal. I am getting the error on a form without
subforms or comboboxes. I tried your suggestion on the before update, after
update and on dirty events on the form and continued to get the error.
Please note that I also get the same error when entering data directly into
the table without using a form. If I remove the update trigger on the table I
do not get the error on the table or the form. I think that I need to do
something with that trigger but I am not sure what!


"strive4peace" wrote:



before your SQL statements, save the record on your form

if me.dirty then me.dirty = false

if you are using subforms

if me.subformcontrolname.form.dirty then
me.subformcontrolname.form.dirty = false

Ff you have comboboxes or listboxes getting criteria from a
query based on the SQL Server table you are trying to update
that get their criteria from your form, use the form
OnCurrent event (and AfterUpdate event of affected controls)
to rewrite the SQL in the combo/listbox.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


Ernie wrote:


I need your help ...please!!!

Can anyone tell me why I'm getting the following write conflict error when I
try to insert a new record in a SQL Server table:

""This record has been changed by another user since you started editing it.
If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to make
changes.""

There is an UPDATE trigger on the table I'm updating and if I remove it I do
not get the conflict error. If I click "Discard Changes" and then refresh,
the data is in both tables.

I assume the problem is in the trigger rather than the forms because I can
get the same error trying to enter data directly into the table.

This is the trigger code:

ALTER TRIGGER tblclaimaction_insert
ON dbo.tblclaimaction
FOR INSERT
AS

INSERT INTO dbo.tbllineclaimamount
(tbllineID, actiondate, parts, labor, misc, cstpart,
dlrpart, comments)
SELECT tbllineID, actiondate, parts, labor, misc, cstpart, dlrpart,
comments

FROM inserted

WHERE (tbllinestatusID = 15) OR
(tbllinestatusID = 16) OR
(tbllinestatusID = 22)

INSERT INTO dbo.tblacesdetail
(tbllineID, tbllinestatusID, actiondate, parts, labor,
misc, cstpart, dlrpart, comments, tblacesID)
SELECT tbllineID, tbllinestatusID, actiondate, parts, labor, misc,
cstpart, dlrpart, comments, tblacesID

FROM inserted

WHERE (tbllinestatusID = 10) OR
(tbllinestatusID = 11) OR
(tbllinestatusID = 12) OR
(tbllinestatusID = 14) OR
(tbllinestatusID = 17) OR
(tbllinestatusID = 18) OR
(tbllinestatusID = 19)


UPDATE tblline SET tbllinestatus = tbllinestatusID
FROM inserted
Where tblline.tbllineID = inserted.tbllineID

This trigger is basically copying the data entered into the first table
(tblclaimaction) one of the other tables (tblacesdetail) or
(tbllineclaimamount), dependant on the data entered. I have a time stamp
field in the first table and have tried the trigger with/without copying the
timestamp. What I do notice though, is there is no value in the time stamp
field. What's with that?

Any assistance would be greatly appreciated!



.