RE: [MS_AccessPros] Recordset not updatable

Are any of the SQL statements the Record Source of FrmJob? If so, please tell us specifically which one. If not, please provide the SQL of the record source and tell us about any relationships between the tables in the form record source.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: drtolson@verizon.net
Date: Mon, 12 Sep 2011 00:12:26 +0000
Subject: [MS_AccessPros] Recordset not updatable

Great Day, I have a form with two subforms that creates an estimate for a customer who is having his/her vehicle serviced at auto repair shop. When I click btnRepairOrder, I get the following message:

Run-time error `3326':
This Recordset is not updatable

Here is the code for opening the form

Private Sub btnRepairOrder_Click()
Dim lCnt As Long
lCnt = DCount("[VehicleID]", "tblVehicles", "[CustomerID]= " & Forms!frmCustomerContactData!txtCustomerID)
'Select Case lCnt
If lCnt = 0 Then
'Case 0
MsgBox "Please enter a vehicle for this customer before proceeding", vbInformation
Exit Sub
Else
Dim mbResponse As Integer
mbResponse = MsgBox("Is the " & Forms!frmCustomerContactData!frmCustomerVehiclesSub.Form!txtVehicleMake & " " _
& Forms!frmCustomerContactData!frmCustomerVehiclesSub.Form!txtVehicleModel & " the vehicle you want to create a repair order for?", vbYesNo, "Correct Vehicle?")
If mbResponse = vbYes Then
' Open the form filtered for the current customer
DoCmd.OpenForm "frmJob"
DoCmd.GoToRecord acDataForm, "frmJob", acNewRec
' Make sure the form is there
Forms!FrmJob.SetFocus
End If
Exit Sub

End If
End Sub

Here is the SQL for the master form which displays customer contact info – name, address, and number – along with the make/model/year of vehicle that is being serviced:

SELECT tblJobs.JobID, tblJobs.CustomerID, tblMasterCustomer.FirstName, tblMasterCustomer.LastName, tblMasterCustomer.[Street Address-Line 1], tblMasterCustomer.[Street Address-Line 2], tblMasterCustomer.City, tblMasterCustomer.State, tblMasterCustomer.ZipCode, tblMasterCustomer.HomePhone, tblMasterCustomer.BusinessPhone, tblMasterCustomer.MobilePhone, tblMasterCustomer.EmailAddress, tblJobs.VehicleID, tblVehicles.MakeID, tblVehicles.ModelID, tblVehicles.CustomerID, tblVehicles.ModYear, tblVehicles.Color, tblJobs.JobTypeID, tblJobs.PartID, tblJobs.RepairDateIn, tblJobs.RepairDateOut, tblJobs.WarrantyWorkPerformed, tblJobs.DateOfWarrantyWork, tblJobs.DescribeWarrantyWork, tblJobs.RepairMilageIn, tblJobs.RepairMileageOut, tblJobs.EmployeeID AS tblRepairOrder_EmployeeID, tblJobs.PaymentMethod, tblJobs.Last5Digits, tblJobs.MiscellaneousCost
FROM tblMasterCustomer INNER JOIN (tblVehicles INNER JOIN tblJobs ON tblVehicles.VehicleID = tblJobs.VehicleID) ON (tblMasterCustomer.CustomerID = tblJobs.CustomerID) AND (tblMasterCustomer.CustomerID = tblVehicles.CustomerID);

Here is the SQL for the 1st subform that displays the services that were performed – oil change, new battery installed, brakes checked, etc.:

SELECT tblScopeofJob.WorkRequestedID, tblScopeofJob.JobId, tblScopeofJob.WorkRequested, tblScopeofJob.WorkRequestedCost, tblScopeofJob.WorkRequestedCost AS RepairCost, tblScopeofJob.Estimate, tblScopeofJob.RepairOrder, tblScopeofJob.Recommended
FROM tblScopeofJob;

Here is the SQL for the 2nd subform that displays the parts needed for the job:

SELECT tblPartsUsed.PartUsedID, tblPartsUsed.JobID, tblPartsUsed.PartID, tblParts.Part, tblPartsUsed.PartCost, tblPartsUsed.PartQty, ([PartCost]*[PartQty]) AS PartTotal
FROM tblParts INNER JOIN tblPartsUsed ON tblParts.[PartID] = tblPartsUsed.[PartID];

The query runs, but I am getting the message that the form is not updatable.

Thanks in advance for your assistance.

r/David

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/