Can't save record - write conflict

Louverril
Louverril used Ask the Experts™
on
I have a quote costing form from which you can accept a quote by clicking the acpt quote button and the following code runs

Dim strResponse As String
Dim rst As DAO.Recordset

'don't try to accept if the currentrecord has not been successfully updated.
If Me.Dirty Then
Me.Dirty = False
End If

Set rst = Me.RecordsetClone
'check if there is already an accepted quote
rst.FindFirst "[tblQuotecosting]![QC_Accepted] = True"
If rst.NoMatch Then
'warn and if ok accept the quote
strResponse = MsgBox("Are you sure you wish accept this quote as the contracted quote?" & vbCrLf & vbCrLf & "Once you " _
& "accept the quote the quoted contracted price is fixed.", vbOKCancel, "WARNING!! Management System")

If strResponse = vbOK Then
Me.txtQC_Accepted.Value = True
With Me.RecordsetClone
.Edit
!QC_TotalContingency = txtQC_TotalContingency
!QC_TotalProfit = txtQC_TotalProfit
!QC_ItemVAT = txtQC_ItemVAT
!QC_ItemLabour = txtQC_ItemLabour
!QC_TotalBuildCostincVAT = txtQC_quoteBuildTotal
!QC_SConCostincVAT = txtQC_SConCostIncVat
!QC_Price = txtQC_Price
!QBuildCostincVAT = txtQC_quoteBuildTotal
!QSubContincVAT = txtQC_SConCostIncVat
!QPriceincVAT = txtQC_Price
.Update
.Close
End With
End If
Else
MsgBox "There is already an accepted Quote for this contract.", 48, "Management System"
End If


This appears to work fine and the QuoteAccepted field on the form shows as ticked.

However you could then decide to copy the quote. By clicking on the button that runs the code further below.

Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
Dim strlinkcriteria As String
Dim strResponse As String

On Error GoTo ANEError:

strlinkcriteria = "[QCI_quoteID]=" & Me![txtQC_quoteID]

'don't try to copy if the currentrecord has not been successfully updated.
If Me.Dirty Then
Me.Dirty = False
End If

'
With Me.RecordsetClone
' If a quote is not already accepted start the copy process
.FindFirst "[QC_Accepted] = True"
If .NoMatch = False Then
strResponse = MsgBox("There is already an accepted quote for this contract." & vbCrLf & vbCrLf & "Do you " _
& "still want to copy?", vbOKCancel, "WARNING!! Access North East Management System")
If strResponse = vbCancel Then
GoTo ANEExit
End If
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "There is no saved quote to copy - please dislay a valid quote.", 48, "Management System"
Else
'Duplicate the main record: add to form's clone. Note the quote accepted indicator is not duplicated, nor any of the totals which are only stored when hte quote is accepted

.AddNew
!QC_ContractID = Me.QC_ContractID 'copy values
!QC_QuoteReference = Me.QC_QuoteReference & "/Rev"
!QC_ContPercentApplied = Me.QC_ContPercentApplied
!QC_ProfitPercentApplied = Me.QC_ProfitPercentApplied
!QC_VATRateApplied = Me.QC_VATRateApplied
!QC_LabourFactor = Me.QC_LabourFactor
!QC_LoftWidth = Me.QC_LoftWidth
!QC_LoftHeight = Me.QC_LoftHeight
!QC_LoftLength = Me.QC_LoftLength
!QC_Dor1Width = QC_Dor1Width
!QC_Dor1Height = QC_Dor1Height
!QC_Dor1Length = QC_Dor1Length
!QC_Dor2Width = QC_Dor2Width
!QC_Dor2Height = QC_Dor2Height
!QC_Dor2Length = QC_Dor2Length
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !QC_QuoteID

'Duplicate the related quote item records:
If DCount("QCI_quoteID", "tblQuoteCostingItem", strlinkcriteria) > 0 Then 'copy the second level values. Should always be related items at this stage but good practive to check.
strSql = "INSERT INTO [tblQuoteCostingItem] ( QCI_quoteID, QCI_itemcategory, QCI_SC, QCI_SpecialCalcInd, QCI_itemdescription, QCI_number, " & _
"QCI_meterage, QCI_itemunitprice, QCI_netCost, QCI_contingency, QCI_profit, " & _
"QCI_VAT, QCI_labour, QCI_grossCost ) " & _
"SELECT " & lngID & " As NewID, QCI_itemcategory, QCI_SC, QCI_SpecialCalcInd, QCI_itemdescription, QCI_number, " & _
"QCI_meterage, QCI_itemunitprice, QCI_netCost, QCI_contingency, QCI_profit, " & _
"QCI_VAT, QCI_labour, QCI_grossCost " & _
"FROM [tblQuoteCostingItem] WHERE QCI_quoteID = " & Me.txtQC_quoteID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError

Else
MsgBox "Quote duplicated, but there were no related Quote Costing Items.", 48, "Management System"
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End If
End With

ANEExit:
Me.RecordsetClone.Close



My problem is that if you do this you get a "write conflict" error pop up - see attached gif. I have tried requerys and docmd.runcomandsave's in various locations but I can't get the record to save after it's be accepted - so I don't get the write conflict. Even making some changed to the fields on the form before pressing the copy doesn't seem to work. I also have this code on the on current event of the quotes form - it open a list of quote items in a form. Not sure if this has anything to do with the problem - just thought I'd mention it.

Any ideas?



Dim strlinkcriteria As String '
Dim Strdocname As String
Dim strSql As String
Dim strQuoteID As String

strQuoteID = Me.txtQC_quoteID
Strdocname = "frmQuoteCostingItem"
strlinkcriteria = "[QCI_quoteID]=" & Me![txtQC_quoteID]

If CurrentProject.AllForms(Strdocname).IsLoaded = False Then
If DCount("QCI_quoteID", "tblQuoteCostingItem", strlinkcriteria) = 0 Then 'check if this is the first quote item _
(cannot use strlinkcriteria) and if so open form in add mode.
DoCmd.OpenForm Strdocname, acNormal, , , acFormAdd
strSql = "INSERT INTO [tblQuoteCostingItem] ( QCI_quoteID, QCI_Itemcategory, QCI_SC, QCI_SpecialCalcInd, QCI_Itemdescription, QCI_number, QCI_meterage, QCI_itemunitprice ) " & _
"SELECT " & strQuoteID & " As quoteID, [ConfigQC_Itemcategory], [ConfigQC_SC], [ConfigQC_SpecialCalcInd], [ConfigQC_Itemdescription], [ConfigQC_number] ,[ConfigQC_meterage], [ConfigQC_itemunitprice] " & _
"FROM [tblConfigQuoteCostingItem]"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If
DoCmd.OpenForm Strdocname

End If

Forms(Strdocname).Filter = strlinkcriteria
Forms(Strdocname).FilterOn = True

Me.sfrmQuoteCostingCategorySum.Requery 'refesh the data in the category summary form.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You could try closing the recordset before running the execute statement to make sure it releases its lock from doing the update.

Hi  Lou,
see Allen Browne's version of what you are trying to do.  Your code and Allen's code are very similar, but there are differences that result in your code having a write conflict.
                    http://allenbrowne.com/ser-57.html

                     

Author

Commented:
jrm  - thanks, but  this made no difference. The copy code works fine in every other situation - other than when run just after the accept quote.

I also have the same write conflict if I run the accept quote a second time in succession.

puppydog - thanks. Unfortunately my items form cannot be a subform - and that's the only real difference in the code. Anyway as I have discoverd it seems to be the accept quote code that is the problem.


Lou

Author

Commented:
I spotted I had the close recordset command in the wrong  place on the accept button and changed it as below (moved to end) but still got the same problem

   On Error GoTo ANEError
   
    Dim strResponse As String
    Dim rst As DAO.Recordset
     
    'don't try to accept if the currentrecord has not been successfully updated.
    If Me.Dirty Then
        Me.Dirty = False
    End If
   
    Set rst = Me.RecordsetClone
    'check if there is already an accepted quote
    rst.FindFirst "[tblQuotecosting]![QC_Accepted] = True"
    If rst.NoMatch Then
      'warn and if ok accept the quote
      strResponse = MsgBox("Are you sure you wish accept this quote as the contracted quote?" & vbCrLf & vbCrLf & "Once you " _
      & "accept the quote the quoted contracted price is fixed.", vbOKCancel, "WARNING!! t Management System")
     
      If strResponse = vbOK Then
          Me.txtQC_Accepted.Value = True
          With Me.RecordsetClone
              .Edit
                  !QC_TotalContingency = txtQC_TotalContingency
                  !QC_TotalProfit = txtQC_TotalProfit
                  !QC_ItemVAT = txtQC_ItemVAT
                  !QC_ItemLabour = txtQC_ItemLabour
                  !QC_TotalBuildCostincVAT = txtQC_quoteBuildTotal
                  !QC_SConCostincVAT = txtQC_SConCostIncVat
                  !QC_Price = txtQC_Price
                  !QBuildCostincVAT = txtQC_quoteBuildTotal
                  !QSubContincVAT = txtQC_SConCostIncVat
                  !QPriceincVAT = txtQC_Price
              .Update
          End With
       End If
    Else
        MsgBox "There is already an accepted Quote for this contract.", 48, "Management System"
    End If
   
    Me.RecordsetClone.Close

Author

Commented:
If you select to save the record on the write conflict error pop up then you have no more problems. Also if you close the pop up or select drop changes,

If you select the option to copy to clip board you get the error

"3021 No current Record"

By the way I changed the close in the previous post to
rst.Close
Set rst = Nothing

Didn't help.
   

Author

Commented:
I had two recordsets open in the code .. sorted this but still have the same problem (user gets the write conflict box when either trying to accept the quote again or closing the form) . I have isolated it to the accept quote code - which is now:

On Error GoTo ANEError
   
    Dim strResponse As String
       
   'don't try to accept if the currentrecord has not been successfully updated.
    If Me.Dirty Then
        Me.Dirty = False
    End If

 
    With Me.RecordsetClone
        'check if there is already an accepted quote
        .FindFirst "[tblQuotecosting]![QC_Accepted] = True"
       If .NoMatch Then
          'warn and if ok accept the quote
          strResponse = MsgBox("Are you sure you wish accept this quote as the contracted quote?" & vbCrLf & vbCrLf & "Once you " _
          & "accept the quote the quoted contracted price is fixed.", vbOKCancel, "WARNING!! Access North East Management System")

          If strResponse = vbOK Then
            Me.txtQC_Accepted.Value = True
                 .Edit
                    !QC_TotalContingency = txtQC_TotalContingency
                    !QC_TotalProfit = txtQC_TotalProfit
                    !QC_ItemVAT = txtQC_ItemVAT
                    !QC_ItemLabour = txtQC_ItemLabour
                    !QC_TotalBuildCostincVAT = txtQC_quoteBuildTotal
                    !QC_SConCostincVAT = txtQC_SConCostIncVat
                    !QC_Price = txtQC_Price
                    !QBuildCostincVAT = txtQC_quoteBuildTotal
                    !QSubContincVAT = txtQC_SConCostIncVat
                    !QPriceincVAT = txtQC_Price
                .Update
          End If
          Else
            MsgBox "There is already an accepted Quote for this contract.", 48, "Access North East Management System"
       End If
        .Close
    End With
   
    DoCmd.Close acForm, "frmQuoteCostingItem"
    Exit Sub

ANEError:
    MsgBox Err.Number & " " & Err.description
    Resume Next
Lou,
If there is already an accepted Quote for this contract,undo all data entered on the form before closing it.
If Me.Dirty Then
      Me.Undo
 End If
DoCmd.Close acForm, "frmQuoteCostingItem"
 Exit Sub
   

Author

Commented:
puppy dog - thanks, tried this but  the  user still gets the write conflict before the record can be saved - no matter what the scenario is.
Lou,
If the write conflict is before the undo of the form, then use the CancelUpdate method in place of the Update method for the recordset.

.Edit
                    !QC_TotalContingency = txtQC_TotalContingency
                    !QC_TotalProfit = txtQC_TotalProfit
                    !QC_ItemVAT = txtQC_ItemVAT
                    !QC_ItemLabour = txtQC_ItemLabour
                    !QC_TotalBuildCostincVAT = txtQC_quoteBuildTotal
                    !QC_SConCostincVAT = txtQC_SConCostIncVat
                    !QC_Price = txtQC_Price
                    !QBuildCostincVAT = txtQC_quoteBuildTotal
                    !QSubContincVAT = txtQC_SConCostIncVat
                    !QPriceincVAT = txtQC_Price
                .CancelUpdate

Author

Commented:
puppydog

Thanks for your patience!

Just to clarify - the problem is that the accept code does not result in the accepted record being saved. It runs through and displays a tick/check in the accepted?  (Y/N) field but the record behind has not been saved.

So when the code is run for the second time  and it gets to the  "me.dirty =false" OR if the user tries to close the form the write conflict  pop up appears.

This happens even if the user has not updated anything in between clicks of the accept button. I just can't understand why after the first run of the code I can't save the record.

Even on the first run through you get the write conflict pop up if you add "DoCmd.RunCommand acCmdSave" to the end of the code.

So somehow it thinks its being edited elsewhere. I have tried closing down the QuoteCostingITems form before running the code and altering the Quote form on current event above so it doesn't open again (as the Quote for oncurrent fires during the accept quote process)

I can't see how canceling the update will help - I need to be able to save the original update and store some values to the table. That's what I don't seem to be able to do.

PS SOLVED IT, OR AT LEAST FOUND THE REASON. The quotes form is opened from the manage contracts form. The contracts table is included in the datasource for the quotes form. Writing the above made me realise it might be this that was causing the problem. And it was - if I close the manage contract form down first the record is saved OK.

I will have to close the manage contracts form down and reopen it. Unless you can think of anything?

Thanks Lou

Author

Commented:
PPS I also will have to keep teh quote items form closed. Which is a pain.
I think all you need to do is place Me.Dirty = False statement just before you open the quotes form and that should save any pending items on the first form, then you won't have a conflict.

Author

Commented:
Tried that already -  didn't work.

I'm not so sure closing the forms is working either. I seem to be having problems again even though both of the other forms are closed when the update occurs.

Are you using bound forms?  

Author

Commented:
Yes - although some of the fields on the quote summary are unbound (the calculated fields) . Hence the updates.
You need make the entire form unbound and update all the fields strictly thru the recordset.  that it why you are getting the conflict.

Author

Commented:
Closing the manage contracts form only solves the issue if the quote has been previously added - so they were added the quote form was closed and opened again. . I.e. the code that runs on the on current event has added an (almost blank) quote record and/or (not sure if I could test this) the code that add the quote costing items on the on current of the quotes form has run in the past.

I'm not sure I can make it all unbound - are you sure this would work?

Yes....confident.
See these links before you start:
           http://www.databaselessons.com/unbound-forms-1.php
           http://www.databaselessons.com/unbound-forms-2.php

Author

Commented:
The problem is I am in the process of "rescuing" a system written by someone else. I wish now I had started from scatch but I didn't.

None of the forms have add or save buttons.


It's very  unlikely anyone would accept the quote when it's the first time the quote has been opened.

It would be fine I think if I could test whether its in the newly added state where I get the problem.

Any ideas how I could test? me. dirty doesn't do it nor does a find first on the quoteid - it finds it (even though the record is not yet in the table).

This is the findfirst statement I am using:
.FindFirst "[tblQuotecosting]![QC_QuoteID] = " & Me.txtQC_quoteID & ""



Lou,
If you want to test if a record exists in the table, you can use the DCount function:
 If DCount(A1,A2,A3 ) > 0 then  <<<<fill in the arguments                            
Puppydog

Yes thanks. This has the same result as the findfirst - it finds the record.

But I have solved the problem - definitely this time.

It boiled down to the update statements. I took out the   'Me.txtQC_Accepted.Value = True before the .edit and updated the QC_Accepted value through the recordset like the other values.

       If StrResponse = vbOK Then
                 .Edit
                    !QC_TotalContingency = txtQC_TotalContingency
                    !QC_TotalProfit = txtQC_TotalProfit
                    !QC_ItemVAT = txtQC_ItemVAT
                    !QC_ItemLabour = txtQC_ItemLabour
                    !QC_TotalBuildCostincVAT = txtQC_quoteBuildTotal
                    !QC_SConCostincVAT = txtQC_SConCostIncVat
                    !QC_Price = txtQC_Price
                    !QBuildCostincVAT = txtQC_quoteBuildTotal
                    !QSubContincVAT = txtQC_SConCostIncVat
                    !QPriceincVAT = txtQC_Price
                    !QC_Accepted = True
                .Update
          End If

Thanks for your input on this.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial