Write Conflict / Default Yes

drewcp · Oct 15, 2008

This error message pops up on me when i click on my list box

and it should be popping up because right before i ask the data to be saved to my table i am erasing the old data in the table, so it is performing exactly what i want and how i want it

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

PHV · Oct 15, 2008

What is your code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

drewcp · Oct 16, 2008

I actually don't have the code anymore because i ended up changing the database last night and took this part out.

Sorry i couldn't help you in helping me, but thank you for your response

if you had any ideas for this though, givin the information above, so if others have the same problem they don't have to start a new thread that would probably be ideal

9milla · Nov 18, 2008

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 im 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 massage 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 'NO'.

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:

Code:

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

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

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

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

    [navy]If[/navy] (rsExpComment.RecordCount = 1) [navy]And[/navy] (rsClientCode.RecordCount = 1) Then
        [navy]Dim[/navy] rsExpAdd [navy]As[/navy] ADODB.Recordset
        [navy]Dim[/navy] stExpAdd [navy]As String[/navy]
        [navy]Dim[/navy] lngErrNumber [navy]As Long[/navy]
        
        [navy]Set[/navy] rsExpAdd = [navy]New[/navy] ADODB.Recordset
        stExpAdd = "SELECT * FROM [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(Now(), "dd/mm/yyyy")
			rsExpAdd!TransactionDate = Format(Now(), "dd/mm/yyyy")
			rsExpAdd.Update
			
			rsExpAdd.Close
        rsExpComment.Close

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

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

Exit_BtnUpdate1_Click:
     [navy]Exit Sub[/navy]

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

Below is the trigger's t-sql:

Code:

[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]
AFTER [blue]INSERT
AS
SET NOCOUNT ON 
IF[/blue]([blue]UPDATE[/blue](PayID))
	[blue]IF[/blue] [gray]EXISTS[/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
					[blue]WHERE[/blue] dbo.Clients.Expenses = 1
					[gray]AND[/gray] Inserted.PayRefDesc [gray]LIKE[/gray] [red]'EXP-%'[/red])
		[blue]BEGIN 
			IF[/blue] [gray]EXISTS[/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
						[blue]WHERE[/blue] Inserted[blue].Value[/blue] >(630-dbo.Debtors.[Total Expenses]))
				[blue]BEGIN
					ROLLBACK TRANSACTION
					RETURN	
				END
			ELSE
				DECLARE[/blue] 
					@intClaimNo [blue]INT[/blue],
					@txtPayRefDesc [blue]NVARCHAR[/blue](20)

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

				[blue]IF[/blue](@txtPayRefDesc = [red]'EXP-CALL'[/red])
					[blue]IF[/blue] [gray]EXISTS[/gray]([blue]SELECT[/blue] [fuchsia]COUNT[/fuchsia] (dbo.[Payments Table].ClaimNo)
								[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](MM, [Payments Table].Date) = [fuchsia]DATEPART[/fuchsia](MM, GETDATE())
								[gray]AND[/gray] [fuchsia]DATEPART[/fuchsia](YY, [Payments Table].Date) = [fuchsia]DATEPART[/fuchsia](YY, GETDATE())
								[blue]HAVING[/blue] [fuchsia]COUNT[/fuchsia] (dbo.[Payments Table].ClaimNo) >= 2)
						[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](@txtPayRefDesc = [red]'EXP-EMAIL'[/red])
					[blue]IF[/blue] [gray]EXISTS[/gray]([blue]SELECT[/blue] [fuchsia]COUNT[/fuchsia] (dbo.[Payments Table].ClaimNo)
								[blue]FROM[/blue] dbo.[Payments Table]
								[blue]WHERE[/blue] dbo.[Payments Table].ClaimNo = @intClaimNo
								[gray]AND[/gray] dbo.[Payments Table].PayRefDesc = 'EXP-EMAIL'
								[gray]AND[/gray] [fuchsia]DATEPART[/fuchsia](MM, [Payments Table].Date) = [fuchsia]DATEPART[/fuchsia](MM, GETDATE())
								[gray]AND[/gray] [fuchsia]DATEPART[/fuchsia](YY, [Payments Table].Date) = [fuchsia]DATEPART[/fuchsia](YY, GETDATE())
								[gray]HAVING[/gray] [fuchsia]COUNT[/fuchsia] (dbo.[Payments Table].ClaimNo) >= 2)
						[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](@txtPayRefDesc = [red]'EXP-FAXS'[/red])
					[blue]IF[/blue] [gray]EXISTS[/gray]([blue]SELECT[/blue] [fuchsia]COUNT[/fuchsia] (dbo.[Payments Table].ClaimNo)
								[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](MM, [Payments Table].Date) = [fuchsia]DATEPART[/fuchsia](MM, GETDATE())
								[gray]AND[/gray] [fuchsia]DATEPART[/fuchsia](YY, [Payments Table].Date) = [fuchsia]DATEPART[/fuchsia](YY, GETDATE())
								[blue]HAVING[/blue] [fuchsia]COUNT[/fuchsia] (dbo.[Payments Table].ClaimNo) >= 2)
						[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
							[gray]ON[/gray] dbo.Debtors.ClaimNo = Inserted.ClaimNo
						[blue]END

				IF[/blue](@txtPayRefDesc = [red]'EXP-LTSC'[/red])
					[blue]IF[/blue] [gray]EXISTS[/gray]([blue]SELECT[/blue] [fuchsia]COUNT[/fuchsia] (dbo.[Payments Table].ClaimNo)
								[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](MM, [Payments Table].Date) = [fuchsia]DATEPART[/fuchsia](MM, GETDATE())
								[gray]AND[/gray] [fuchsia]DATEPART[/fuchsia](YY, [Payments Table].Date) = [fuchsia]DATEPART[/fuchsia](YY, GETDATE())
								[blue]HAVING[/blue] [fuchsia]COUNT[/fuchsia] (dbo.[Payments Table].ClaimNo) >= 2)
						[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
							[gray]ON[/gray] dbo.Debtors.ClaimNo = Inserted.ClaimNo
						[blue]END
			END
	IF[/blue] [gray]EXISTS[/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
				[gray]ON[/gray] dbo.Debtors.ClaimNo = Inserted.ClaimNo
				[gray]WHERE[/gray] dbo.Clients.Expenses = 0
				[gray]AND[/gray] Inserted.PayRefDesc [gray]LIKE[/gray] [red]'EXP-%'[/red])
		[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)

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Write Conflict / Default Yes

drewcp

Programmer

PHV

MIS

drewcp

Programmer

9milla

Programmer

Similar threads

Part and Inventory Search

Sponsor