Supress Write Conflict error message in Access 2003

[9milla]

Hi

As the Subject reads.

How do I 'Supress Write Conflict error message in Access 2003'?

I have posted my question and relevant code in another thread and don't think it will be necessary for me to repost it here.

Here is the thread thread702-1507221

Apologies for any confusion.

Many thanks
9milla




"And so it begins!"
LOTR (Battle for Helms Deep)

 

[9milla]

Ok...so for the convenience of everything, I've decided to repost my original question here in hope that it will get a solution.

Original Post

I have a very similar problem and wanting to achieve the same result as drewcp.

..is there a way though to have it default click Save Record so that the user does not even see the box?

My problem is bit more complex as it includes a trigger on one of the tables I'm updating in SQL 2005.

Here's my problem.

I have a user form with a tabbed control which has linked fields. On the second tab I have 2 continuous subforms that also have linked fields.

Scenario:
When the user inserts a certain comment-type from a drop down listbox and clicks an update button, all changes on the current form updates. Another recordset is opened and updates another table.

The problem is that I have a trigger residing on the second table which gets updated via the recordest. The trigger determines whether or not the submitted data will be updated or not depending on the current data in the the table. if it fails, the trigger rollsback and cancels the transaction.

When testing the t-sql of the trigger, everything runs fine as it's suppose to, however when using the update method from the form's update button, I get the same 'write conflict' error message being displayed to the user.

I would prefer that the default option be set to save as it does so anyway for the data being updated, or just to supress the message from being displayed as the trigger does all the dirty work.

I have being to numerous forums all of which dont cater a solution to my problem.

I have checked that all bit fields in my tables have default values and dont allow 'Nulls' and have also set the 'NOCOUNT' property in the t-sql to 'ON'.

I have gone as far as to read up on Replication Conflicts which don't seem be of any assistance.

I am using MS Access 2003 as my front end in a .adp format and SQL Server 2005 as mentioned before for my backend.

My code is as follows:

[blue]Private Sub[/blue] BtnUpdate1_Click[gray]([/gray][gray])[/gray]
[blue]On Error GoTo[/blue] Err_BtnUpdate1_Click
    
    [blue]Dim[/blue] aradb [blue]As[/blue] Connection
    [blue]Dim[/blue] comrst [blue]As[/blue] ADODB.Recordset
    [blue]Dim[/blue] strRstDate [blue]As String[/blue] 
    
    [blue]Set[/blue] aradb = CurrentProject.Connection
    [blue]Set[/blue] comrst = [blue]New[/blue] ADODB.Recordset
   [green]
    '|                _________________________________________________
    '|_______________/              Update Comment's Table             \___________________________
    '|¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯\_________________________________________________/¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
    '|                ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[/green]
    comrst.Open "Select * [blue]FROM[/blue] [Comments Table] where 1 = 2", aradb, adOpenDynamic, adLockOptimistic
    [blue]If[/blue] Comment = "OOOC" [blue]Or[/blue] Comment = "OOOH" [blue]Or[/blue] Comment = "OOOW" [blue]Then[/blue]
        [blue]If[/blue] IsNull[gray]([/gray]Me.Remarks.Value[gray])[/gray] [blue]Then[/blue]
                Me.Remarks.Value = "TRY OTHER NUMBERS"
            [blue]Else[/blue]
            [blue]End [blue]If[/blue][/blue]
        [blue]End [blue]If[/blue][/blue]

        [blue]If[/blue] Comment = "TRS" [blue]Then[/blue]
            [blue]If IsNull[/blue][gray]([/gray]Me.Remarks.Value[gray])[/gray] [blue]Then[/blue]
                Me.Remarks.Value = "SEND FOR EXTERNAL\INTERNAL TRACING"
            [blue]Else
            End If                                                                                       
        End If[/blue]

        comrst.AddNew
        [blue]If[/blue] Comment Like "PTP*" [blue]Then[/blue]
            comrst![ClaimNo] = Me![ClaimNo]
            comrst![Date] = Date
            comrst![RefNo] = Me![CollectorNo]
            comrst![Comment] = Me![Comment]
            comrst![Remark1] = Me![Remarks]
            comrst![PTPDate] = Me![PTPDate]                                                              
            comrst![PTPAmount] = Me![ptpamt]                                                             
        [blue]Else[/blue][blue]If[/blue] Comment <> "PTP*" [blue]Then[/blue]
            comrst![ClaimNo] = Me![ClaimNo]
            comrst![Date] = Date
            comrst![RefNo] = Me![CollectorNo]
            comrst![Comment] = Me![Comment]
            comrst![Remark1] = Me![Remarks]
        [blue]End [blue]If[/blue][/blue]
    comrst.Update
    comrst.Close
       
    [green]'Add Expense Charge[/green]
    
    [blue]Dim[/blue] rsClientCode [blue]As[/blue] ADODB.Recordset
    [blue]Dim[/blue] rsExpComment [blue]As[/blue] ADODB.Recordset
    [blue]Dim[/blue] stClientCode [blue]As String[/blue]
    [blue]Dim[/blue] stExpComment [blue]As String[/blue]
    
    [blue]Set[/blue] rsClientCode = [blue]New[/blue] ADODB.Recordset
    stClientCode = "SELECT Client_Group, ClientName, ReceiptFee " & _
            "FROM dbo.Clients " & _
            "WHERE [gray]([/gray]Expenses = 1[gray])[/gray] AND [gray]([/gray]ClientCode = '" & Me.ClientCode & "'[gray])[/gray]"
    rsClientCode.Open stClientCode, aradb, adOpenDynamic, adLockOptimistic

    [blue]Set[/blue] rsExpComment = [blue]New[/blue] ADODB.Recordset
    stExpComment = "SELECT Code, ExpenseDesc, ExpenseCharge, Description " & _
            "FROM dbo.[Comment-Types] " & _
            "WHERE [gray]([/gray]Expenses = 1[gray])[/gray] AND [gray]([/gray]Code = '" & Me.Comment & "'[gray])[/gray]"
    rsExpComment.Open stExpComment, aradb, adOpenDynamic, adLockOptimistic

    [blue]If[/blue] [gray]([/gray]rsExpComment.RecordCount = 1[gray])[/gray] [blue]And[/blue] [gray]([/gray]rsClientCode.RecordCount = 1[gray])[/gray] [blue]Then[/blue]
        [blue]Dim[/blue] rsExpAdd [blue]As[/blue] ADODB.Recordset
        [blue]Dim[/blue] stExpAdd [blue]As[/blue] String
        [blue]Dim[/blue] lngErrNumber [blue]As[/blue] Long
        
        [blue]Set[/blue] rsExpAdd = [blue]New[/blue] ADODB.Recordset
        stExpAdd = "SELECT * [blue]FROM[/blue] [PAYMENTS TABLE] " & _
                "WHERE 1 = 2"
        
        rsExpAdd.Open stExpAdd, aradb, adOpenDynamic, adLockOptimistic

            rsExpAdd.AddNew
            rsExpAdd!ClaimNo = Me.ClaimNo.Value
            rsExpAdd!ClientCode = Me.ClientCode.Value
            rsExpAdd!FirstAccNo = Me.FirstAccNo.Value
            rsExpAdd!PayRefDesc = rsExpComment!ExpenseDesc
            rsExpAdd!Value = rsExpComment!ExpenseCharge
            rsExpAdd!Date = Format[gray]([/gray]Now[gray]([/gray][gray])[/gray], "dd/mm/yyyy"[gray])[/gray]
            rsExpAdd!TransactionDate = Format[gray]([/gray]Now[gray]([/gray][gray])[/gray], "dd/mm/yyyy"[gray])[/gray]
            rsExpAdd.Update
            
            rsExpAdd.Close
        rsExpComment.Close

        [blue]If[/blue] [gray]([/gray]lngErrNumber = -2147217900[gray])[/gray] [blue]Then[/blue]
            MsgBox "Cant Add Expenses"
        [blue]End [blue]If[/blue][/blue]
    [blue]End [blue]If[/blue][/blue]

    Application.setOption "Confirm Record Changes", 0
    Application.DoCmd.RunCommand acCmdSave
        
    DoCmd.Close

Exit_BtnUpdate1_Click:
     [blue]Exit Sub[/blue]

Err_BtnUpdate1_Click:
    [blue]If[/blue] [gray]([/gray]Err.Number = -2147217900[gray])[/gray] [blue]Then[/blue]
        lngErrNumber = Err.Number
        [blue]Resume Next[/blue]
    [blue]Else[/blue]
        MsgBox Err.Description
        [blue]Resume[/blue] Exit_BtnUpdate1_Click
    [blue]End [blue]If[/blue]
End Sub[/blue]

Below is the trigger's t-sql:

[blue]SET ANSI_NULLS ON[/blue]
GO[blue]
SET QUOTED_IDENTIFIER ON[/blue]
GO
[blue]
ALTER TRIGGER[/blue] [tgrExpenseInsert] [blue]ON[/blue] [dbo].[Payments Table]
[blue]AFTER INSERT
AS
SET NOCOUNT ON
IF[/blue][gray]([/gray][blue]UPDATE[/blue][gray]([/gray]PayID[gray])[/gray][gray])[/gray]
    [blue]IF[/blue] [gray]EXISTS [gray]([/gray][/gray][blue]SELECT[/blue] dbo.Clients.Client_Group, dbo.Debtors.ClaimNo
                    [blue]FROM[/blue] dbo.Clients [gray]INNER JOIN[/gray] dbo.Debtors
                    [blue]ON[/blue] dbo.Clients.ClientCode = dbo.Debtors.ClientCode
                    [gray]INNER JOIN[/gray] Inserted
                    [blue]ON[/blue] dbo.Debtors.ClaimNo = Inserted.ClaimNo
                    [gray]WHERE[/gray] dbo.Clients.Expenses = 1
                    [gray]AND[/gray] Inserted.PayRefDesc [gray]LIKE[/gray] [red]'EXP-%'[/red][gray])[/gray]
        [blue]BEGIN 
            IF[/blue] [gray]EXISTS [gray]([/gray][/gray][blue]SELECT[/blue] dbo.Debtors.CurrentBalance, dbo.Debtors.[Total Expenses]
                        [blue]FROM[/blue] dbo.[Comment-Types] [gray]INNER JOIN[/gray] Inserted 
                        [blue]ON[/blue] dbo.[Comment-Types].ExpenseDesc = Inserted.PayRefDesc
                        [gray]INNER JOIN[/gray] dbo.Debtors
                        [blue]ON[/blue] Inserted.ClaimNo = dbo.Debtors.ClaimNo
                        [gray]WHERE[/gray] Inserted[blue].Value[/blue] >[gray]([/gray]630-dbo.Debtors.[Total Expenses][gray])[/gray][gray])[/gray]
                [blue]BEGIN
                    ROLLBACK TRANSACTION
                    RETURN    
                END
            ELSE
                DECLARE[/blue]
                    @intClaimNo [blue]INT[/blue],
                    @txtPayRefDesc [blue]NVARCHAR[/blue][gray]([/gray]20[gray])[/gray]

                [blue]SELECT[/blue] @intClaimNo = Inserted.ClaimNo, @txtPayRefDesc = Inserted.PayRefDesc
                    [blue]FROM[/blue] Inserted[blue]

                IF[/blue][gray]([/gray]@txtPayRefDesc = [red]'EXP-CALL'[/red][gray])[/gray]
                    [blue]IF[/blue] [gray]EXISTS [gray]([/gray][/gray][blue]SELECT[/blue] [fuchsia]COUNT[/fuchsia] [gray]([/gray]dbo.[Payments Table].ClaimNo[gray])[/gray]
                                [blue]FROM[/blue] dbo.[Payments Table]
                                [blue]WHERE[/blue] dbo.[Payments Table].ClaimNo = @intClaimNo
                                [gray]AND[/gray] dbo.[Payments Table].PayRefDesc = [red]'EXP-CALL'[/red]
                                [gray]AND[/gray] [fuchsia]DATEPART[/fuchsia][gray]([/gray]MM, [Payments Table].Date[gray])[/gray] = [fuchsia]DATEPART[/fuchsia][gray]([/gray]MM, GETDATE[gray][gray]([/gray][gray])[/gray][gray])[/gray][/gray]
                                [gray]AND[/gray] [fuchsia]DATEPART[/fuchsia][gray]([/gray]YY, [Payments Table].Date[gray])[/gray] = [fuchsia]DATEPART[/fuchsia][gray]([/gray]YY, GETDATE[gray][gray]([/gray][gray])[/gray][gray])[/gray][/gray]
                                [blue]HAVING[/blue] [fuchsia]COUNT[/fuchsia] [gray]([/gray]dbo.[Payments Table].ClaimNo[gray])[/gray] [gray]>=[/gray] 2[gray])[/gray]
                        [blue]BEGIN
                            ROLLBACK TRANSACTION
                            RETURN    
                        END
                    ELSE
                        BEGIN
                            UPDATE[/blue] dbo.Debtors
                            [blue]SET[/blue] dbo.Debtors.CurrentBalance = dbo.Debtors.CurrentBalance + Inserted[blue].Value[/blue],
                                dbo.Debtors.[Total Expenses] = dbo.Debtors.[Total Expenses] + Inserted[blue].Value[/blue]
                            [blue]FROM[/blue] dbo.Debtors [gray]INNER JOIN[/gray] Inserted
                            [blue]ON[/blue] dbo.Debtors.ClaimNo = Inserted.ClaimNo[blue]
                        END

                IF[/blue][gray]([/gray]@txtPayRefDesc = [red]'EXP-EMAIL'[/red][gray])[/gray]
                    [blue]IF[/blue] [gray]EXISTS [gray]([/gray][/gray][blue]SELECT[/blue] [fuchsia]COUNT[/fuchsia] [gray]([/gray]dbo.[Payments Table].ClaimNo[gray])[/gray]
                                [blue]FROM[/blue] dbo.[Payments Table]
                                [blue]WHERE[/blue] dbo.[Payments Table].ClaimNo = @intClaimNo
                                [gray]AND[/gray] dbo.[Payments Table].PayRefDesc = [red]'EXP-EMAIL'[/red]
                                [gray]AND[/gray] [fuchsia]DATEPART[/fuchsia][gray]([/gray]MM, [Payments Table].Date[gray])[/gray] = [fuchsia]DATEPART[/fuchsia][gray]([/gray]MM, GETDATE[gray][gray]([/gray][gray])[/gray][gray])[/gray][/gray]
                                [gray]AND[/gray] [fuchsia]DATEPART[/fuchsia][gray]([/gray]YY, [Payments Table].Date[gray])[/gray] = [fuchsia]DATEPART[/fuchsia][gray]([/gray]YY, GETDATE[gray][gray]([/gray][gray])[/gray][gray])[/gray][/gray]
                                [blue]HAVING[/blue] [fuchsia]COUNT[/fuchsia] [gray]([/gray]dbo.[Payments Table].ClaimNo[gray])[/gray] [gray]>=[/gray] 2[gray])[/gray]
                        [blue]BEGIN
                            ROLLBACK TRANSACTION
                            RETURN    
                        END
                    ELSE
                        BEGIN
                            UPDATE[/blue] dbo.Debtors
                            [blue]SET[/blue] dbo.Debtors.CurrentBalance = dbo.Debtors.CurrentBalance + Inserted[blue].Value[/blue],
                                dbo.Debtors.[Total Expenses] = dbo.Debtors.[Total Expenses] + Inserted[blue].Value[/blue]
                            [blue]FROM[/blue] dbo.Debtors [gray]INNER JOIN[/gray] Inserted
                            [blue]ON[/blue] dbo.Debtors.ClaimNo = Inserted.ClaimNo[blue]
                        END

                IF[/blue][gray]([/gray]@txtPayRefDesc = [red]'EXP-FAXS'[/red][gray])[/gray]
                    [blue]IF[/blue] [gray]EXISTS [gray]([/gray][/gray][blue]SELECT[/blue] [fuchsia]COUNT[/fuchsia] [gray]([/gray]dbo.[Payments Table].ClaimNo[gray])[/gray]
                                [blue]FROM[/blue] dbo.[Payments Table]
                                [blue]WHERE[/blue] dbo.[Payments Table].ClaimNo = @intClaimNo
                                [gray]AND[/gray] dbo.[Payments Table].PayRefDesc = [red]'EXP-FAXS'[/red]
                                [gray]AND[/gray] [fuchsia]DATEPART[/fuchsia][gray]([/gray]MM, [Payments Table].Date[gray])[/gray] = [fuchsia]DATEPART[/fuchsia][gray]([/gray]MM, GETDATE[gray][gray]([/gray][gray])[/gray][gray])[/gray][/gray]
                                [gray]AND[/gray] [fuchsia]DATEPART[/fuchsia][gray]([/gray]YY, [Payments Table].Date[gray])[/gray] = [fuchsia]DATEPART[/fuchsia][gray]([/gray]YY, GETDATE[gray][gray]([/gray][gray])[/gray][gray])[/gray][/gray]
                                [blue]HAVING[/blue] [fuchsia]COUNT[/fuchsia] [gray]([/gray]dbo.[Payments Table].ClaimNo[gray])[/gray] [gray]>=[/gray] 2[gray])[/gray]
                        [blue]BEGIN
                            ROLLBACK TRANSACTION
                            RETURN    
                        END
                    ELSE
                        BEGIN
                            UPDATE[/blue] dbo.Debtors
                            [blue]SET[/blue] dbo.Debtors.CurrentBalance = dbo.Debtors.CurrentBalance + Inserted[blue].Value[/blue],
                                dbo.Debtors.[Total Expenses] = dbo.Debtors.[Total Expenses] + Inserted[blue].Value[/blue]
                            [blue]FROM[/blue] dbo.Debtors [gray]INNER JOIN[/gray] Inserted
                            [blue]ON[/blue] dbo.Debtors.ClaimNo = Inserted.ClaimNo
                        [blue]END

                IF[/blue][gray]([/gray]@txtPayRefDesc = [red]'EXP-LTSC'[/red][gray])[/gray]
                    [blue]IF[/blue] [gray]EXISTS [gray]([/gray][/gray][blue]SELECT[/blue] [fuchsia]COUNT[/fuchsia] [gray]([/gray]dbo.[Payments Table].ClaimNo[gray])[/gray]
                                [blue]FROM[/blue] dbo.[Payments Table]
                                [blue]WHERE[/blue] dbo.[Payments Table].ClaimNo = @intClaimNo
                                [gray]AND[/gray] dbo.[Payments Table].PayRefDesc = [red]'EXP-LTSC'[/red]
                                [gray]AND[/gray] [fuchsia]DATEPART[/fuchsia][gray]([/gray]MM, [Payments Table].Date[gray])[/gray] = [fuchsia]DATEPART[/fuchsia][gray]([/gray]MM, GETDATE[gray][gray]([/gray][gray])[/gray][gray])[/gray][/gray]
                                [gray]AND[/gray] [fuchsia]DATEPART[/fuchsia][gray]([/gray]YY, [Payments Table].Date[gray])[/gray] = [fuchsia]DATEPART[/fuchsia][gray]([/gray]YY, GETDATE[gray][gray]([/gray][gray])[/gray][gray])[/gray][/gray]
                                [blue]HAVING[/blue] [fuchsia]COUNT[/fuchsia] [gray]([/gray]dbo.[Payments Table].ClaimNo[gray])[/gray] [gray]>=[/gray] 2[gray])[/gray]
                        [blue]BEGIN
                            ROLLBACK TRANSACTION
                            RETURN  
                        END
                    ELSE
                        BEGIN
                            UPDATE[/blue] dbo.Debtors
                            [blue]SET[/blue] dbo.Debtors.CurrentBalance = dbo.Debtors.CurrentBalance + Inserted[blue].Value[/blue],
                                dbo.Debtors.[Total Expenses] = dbo.Debtors.[Total Expenses] + Inserted[blue].Value[/blue]
                            [blue]FROM[/blue] dbo.Debtors [gray]INNER JOIN[/gray] Inserted
                            [blue]ON[/blue] dbo.Debtors.ClaimNo = Inserted.ClaimNo[blue]
                        END
            END
    IF[/blue] [gray]EXISTS[gray]([/gray][/gray][blue]SELECT[/blue] dbo.Clients.Client_Group, dbo.Debtors.ClaimNo
                [blue]FROM[/blue] dbo.Clients [gray]INNER JOIN[/gray] dbo.Debtors
                [gray]ON[gray] dbo.Clients.ClientCode = dbo.Debtors.ClientCode
                [gray]INNER JOIN[/gray] Inserted
                [blue]ON[/blue] dbo.Debtors.ClaimNo = Inserted.ClaimNo
                [blue]WHERE[/blue] dbo.Clients.Expenses = 0
                [gray]AND[/gray] Inserted.PayRefDesc [gray]LIKE[/gray] [red]'EXP-%'[/red][gray])[/gray]
        [blue]BEGIN
            ROLLBACK TRANSACTION
            RETURN
        END[/blue]
GO

[blue]SET ANSI_NULLS OFF[/blue]
GO[blue]
SET QUOTED_IDENTIFIER OFF[/blue]
GO

Everything runs fine when I step through the code in VBA until I hit the 'Docmd.Close' Event. This results in the 'Wrte Conflict' message being displayed.

If anyone knows of an API call that will allow me to physically set properties for this conflict error or knows of someway to supress it completely will be muchly appreciated.

If there are errors in my code directly relating to my current problem, your feedback is most welcomed.

Many thanks in advance!
9milla



"And so it begins!"
LOTR (Battle for Helms Deep)

 

[RoyVidar]

Not many use adp, so you're perhaps more likely to receive views and replies on your inquiry in a forum dedicated to that tecnology. On this site, it would be forum958.

Also, a search, or usage of the newsgroup microsoft.public.access.adp.sqlserver (you can use googlegroups to access it -

http://groups.google.com/group/microsoft.public.access.adp.sqlserver/topics?lnk=srg&hl=en

could prove worthwhile.

Roy-Vidar

 

[missinglinq]

Just out of curiosity, why are you posting here under two different user names? The link to your previous thread was under the name of drewcp.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[9milla]

Hi RoyVidar and missinglinq

I've been to numerous forums including Google Groups and scoured Microsoft's knowledgebase and still haven't been able to find a way to supress the 'Write Conflict' error message or just to get the properties to accept a Default of 'Save Record'
I've related my problem to another forum on google groups where another user has the same problem and still no-one has been able to resolve.

I'm researching the GetLastActivePopup api function but have yet to find a definitive solution.


The original question was posted by drewcp who I am not.
thread702-1507221: Write Conflict / Default Yes

I just posted a reply having a similar problem. Seeing that I got no response, I linked the thread to this forum hoping someone here might have a solution.

Since no-one replied, I decided to repost my reply to drewcp's original question in hoping that it would make it easier.

Hope this is clear now.

I still however need to know if there is a way to Supress the Write Conlfict message!?

Apologies for any confusion.

Regards,
9milla




"And so it begins!"
LOTR (Battle for Helms Deep)

 

[RoyVidar]

I'd suggest trying to eliminate the cause of the problem, in stead of focusing on removing the symptom.

In other words, redesign the approach, so you don't end up having a write conflict.

This write conflict is probably due to you having a form bound to a table (with pending updates) while you are updating it through code.

Roy-Vidar

 

[missinglinq]

I agree with Roy, this is not an error you can simply make go away by ignoring, you have to resolve the underlying problem or data is going to be lost! To do this would be like having your high engine temperature warning light up on your car's dashboard and instead of checking out why the engine's overheating, simply putting a piece of tape over the warning light so you can't see it!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!