Bound Form and triggers - Write Conflict

A

Angela Law

Hi,

I was wondering if anyone knew how to resolve the write conflict issue I am
recieving with the following query and triggers...

The stored procedure below get the records for a bound form in an ADP file.

ALTER PROCEDURE qryGetEncounters
@pBillingGroupID int,
@pClientID int,
@pPeriodBegin datetime,
@pPeriodEnd datetime
AS
IF @pBillingGroupID<0
BEGIN
SET @pBillingGroupID=NULL
END
IF @pClientID<0
BEGIN
SET @pClientID = NULL
END

SELECT tblEncounters.EncounterID
, tblEncounters.BillingGroupID
, tblEncounters.ClientID
, tblEncounters.ServiceDate
, tblEncounters.PatLastName
, tblEncounters.PatFirstName
, tblEncounters.ChargeID
, tblEncounters.FinancialClassID
, tblEncounters.Quantity
, tblEncounters.Amount
, tblEncounters.DuplicateCharge
, tblEncounters.PostingDate
, tblEncounters.UserID
FROM tblEncounters
WHERE
tblEncounters.BillingGroupID=COALESCE(@pBillingGroupID,tblEncounters.BillingGroupID)
AND tblEncounters.ClientID=COALESCE(@pClientID,tblEncounters.ClientID)
AND tblEncounters.PostingDate between @pPeriodBegin AND @pPeriodEnd


Now these triggers are associated with the table encounters:

ALTER TRIGGER insCorrectDuplicateCharge
ON dbo.tblEncounters
FOR INSERT AS

DECLARE @DuplicateCharge int, @EncounterID int
SELECT @DuplicateCharge = DuplicateCharge, @EncounterID = EncounterID FROM
inserted
IF @DuplicateCharge = 1
BEGIN
DECLARE @There int
SELECT @There = CorrectDuplicateCharge from tblClientsCredits WHERE
AssociatedID = @EncounterID
IF (@There is NULL)
BEGIN
INSERT INTO tblClientsCredits (ClientID,
BillingGroupID,AssociatedID,CreditTypeID,PostingDate,ReceiptDate,CreditDescription,CreditAmount,CorrectDuplicateCharge,CommitTrans,ImportBatchID,UserID)
SELECT ClientID,
BillingGroupID,EncounterID,6,PostingDate,ServiceDate,'Adjustment - Duplicate
Charge',(Quantity*Amount),DuplicateCharge,1,0,UserID FROM inserted
END
END
ELSE
BEGIN
UPDATE tblClientsCredits
SET CorrectDuplicateCharge = 1
WHERE AssociatedID = @EncounterID
END


ALTER TRIGGER updCorrectDuplicateCharge
ON dbo.tblEncounters
FOR update AS

IF UPDATE(DuplicateCharge)
BEGIN
DECLARE @DuplicateCharge int, @EncounterID int
SELECT @DuplicateCharge = DuplicateCharge, @EncounterID = EncounterID FROM
inserted
IF @DuplicateCharge = 1
BEGIN
DECLARE @There int
SELECT @There = CorrectDuplicateCharge from tblClientsCredits WHERE
AssociatedID = @EncounterID
IF (@There is NULL)
BEGIN
INSERT INTO tblClientsCredits (ClientID,
BillingGroupID,AssociatedID,CreditTypeID,PostingDate,ReceiptDate,CreditDescription,CreditAmount,CorrectDuplicateCharge,CommitTrans,ImportBatchID,UserID)
SELECT ClientID,
BillingGroupID,EncounterID,6,PostingDate,ServiceDate,'Adjustment - Duplicate
Charge',(Quantity*Amount),DuplicateCharge,1,0,UserID FROM inserted
END
END
ELSE
BEGIN
DELETE FROM tblClientsCredits WHERE AssociatedID = @EncounterID
END
END


As you can see the triggers themselves has NOTHING to do with changing the
data in the tblencounters.

Yet I am still receiving a write conflict in access when I try to insert a
table into tblencounters.

Can anyone help?

Many thanks,

Angela
 
Top