write conflict error

[Ernie]

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!

 

[strive4peace]

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]

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]

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]

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]

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]

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]

Hi Ernie,

You're welcome

If this set of statements would be used behind another form
or in other places of your program, put the process into a
global function using a general module and call it when you
need it. No need to duplicate code

To make a general module: from the database window, click
the Modules tab and then click the New button.

Even though it does not return a value, declare it to be a
Function -- this way, you can assign it directly to the
property sheet without using an Event Procedure.

ie:
AfterUpdate --> =SQLtrigger()

where SQLtrigger is usually a Public Function in a general
module or in a Private Function in the code behind the form

'~~~~~~~~~~~

Public Function SQLtrigger()

on error goto Err_proc

Dim strSQL as string

strSQL = "INSERT INTO..."
'remove next line after debugged
debug.print strSQL
currentdb.execute strSQL

strSQL = "INSERT INTO..."
'remove next line after debugged
debug.print strSQL
currentdb.execute strSQL

'...more code

Exit_proc:
currentdb.tabledefs.refresh
DoEvents
on error resume next
'... close objects if applicable
'... release variables if applicable
'... msgbox to user if desired
exit function

Err_proc
Msgbox err.description,, _
"ERROR " & err.number & " SQLtrigger"
'press F8 to step thru code and fix problem
'comment next line after done debugging
Stop : Resume
Resume Exit_proc

End Function
'~~~~~~~~~~~

Warm Regards,
Crystal
MVP Microsoft Access

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