Parent Form (based on Query) Write Conflict [Access 2003]

[dgunner71]

All,

I have a Parent form where the Record Source is a query based on three tables (tblcustomers, tblEquipment, and tblWorkOrders).

The Parent form has a subform based on a 4th table (tblServiceVisi ts). When the code from my subform attempts to update controls on the parent form (i.e. refresh the date to the current date and change the status of the work order) I get a write conflict error.

To clarify, I'm getting the error as soon as the parent form gets the focus back. I believe it is because the the information has not been written to the record when the parent form regains focus.

Is there anything along the lines of:

Me.Parent.SaveR ecord

Many thanks in advance for any insight.

Gunner

[patjones]

Can you post the code that you are working with? I'd especially like to see the queries that the parent and sub forms are based on. Thanks.

Pat

[dgunner71]

Pat - Here is the posted code below - this code is located in the subform with the fields to be updated on the parent form. (I changed from Me.Parent to the code below.)


Private Sub dtDateScheduled _AfterUpdate()

Forms!frmWorkOr ders.Form.txtWo rkOrderStatus = "Open Work Order"
Forms!frmWorkOr ders.Form.dtSta tusDate = Now()

End Sub

I'm not 100% sure how I should post the query - I've include the sql below (this is the Record Contorl Source for the parent form):

SELECT tblWorkOrders.* , tblEquipmentSer viced.*, tblCustomers.*, Switch(tblEquip mentServiced!tx tType="Elliptic al","EL",tblEqu ipmentServiced! txtType="Treadm ill","TR",tblEq uipmentServiced !txtType="Home Gym","HG",tblEq uipmentServiced !txtType="Recum bent Bike","RB",tblE quipmentService d!txtType="Upri ght Bike","UB",tblE quipmentService d!txtType="Step per","ST") AS MType, Switch([txtWorkOrderSta tus]="parts on order - Not Confirmed","Not Confirmed",[txtWorkOrderSta tus]="parts on order - Confirmed","Con firmed",[txtWorkOrderSta tus] Like "*Partially*"," Partially Rc'vd",[txtWorkOrderSta tus]="Stock Parts on Order - Confirmed","SP Confrimed",[txtworkordersta tus]="Stock Parts on Order - Not Confirmed","SP Not Confirmed") AS StatusModifier, IIf(nz([dtPurchaseDate],"MT")<>"MT" ,[dtpurchasedate],IIf([numYearsPurchas e]>0,[numYearsPurchas e] & " Year(s)","None Entered")) AS Bought, IIf([txtSecondaryCon tact] & ""="",[txtFirstName] & " " & [txtLastName],[txtFirstName] & " & " & [txtSecondaryCon tact] & " " & [txtLastName]) AS CustFullName
FROM (tblCustomers INNER JOIN tblEquipmentSer viced ON tblCustomers.nu mCustomerID = tblEquipmentSer viced.numCustom erID) INNER JOIN tblWorkOrders ON tblEquipmentSer viced.numEquipm ent = tblWorkOrders.n umEquipment;

The subform (sfrmServiceTri ps) has a control source of tblServiceTrips .

Thanks again for taking the time to help.

Gunner

[dgunner71]

Pat - I got it working by changing the existing script to that below:


CurrentDb.Execu te "Update tblWorkOrders set [txtWorkOrderSta tus] = 'Open Work Order' where numWorkOrderNo = " & Me.Parent.Form. numWorkOrderNo
CurrentDb.Execu te "Update tblWorkOrders set [dtStatusDate] = '" & Now() & "' where numWorkOrderNo = " & Me.Parent.Form. numWorkOrderNo


I found the code here: http://www.utteraccess.com/forum/Sav...-t1857095.html


The was a DBEngine.Idle dbRefreshCache included after the script, but it did not work properly and does not appear to have an adverse effect by commenting it out.

Thanks again for your help.

Gunner

[patjones]

What you posted for dtDateScheduled _AfterUpdate would not work, because if the controls in question, namely txtWorkOrderSta tus and dtStatusDate are already bound to a recordset, trying to explicitly assign values to those controls would be like trying to edit the record without actually telling Access that you are trying to edit the record. Thus you get a write conflict error.

However, what you tried next works because in using the SQL UPDATE, you are telling Access that you want to edit the underlying recordset that the form is based on, and it thus allows you to do so.

Anyway, I'm glad you managed to get it sorted out.

Pat