Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Can't save record - write conflict

Avatar of Louverril
Louverril asked on
Microsoft Access
21 Comments1 Solution628 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Avatar of Louverril
Louverril

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 21 Comments.
See Answers