Minggu, 11 September 2011

RE: [MS_AccessPros] Recordset not updatable

 

Thanks for pushing me in the right direction, with your help, plus the few
of my wife-points I had to spend to figure it out, I figured it out.
Evidently access does not like more than a table or two in a record set. As
you pointed out, I had three tables in my master-form. If I changed the
forms recordset type property from Dynaset to Dynaset (Inconsistent Updates)
it worked. However, my research suggested to me that that is nor the best
way around the situation, so I zeroed in on why I had more than one table
and discovered that my reasoning was off. So I deleted two of the tables in
the Query and that fixed it. I'll have to do more studying on your method of
combo boxes and subforms. Are you aware of any reference material on line
that I can study?

Thanks a million!!

r/David

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
Sent: Monday, September 12, 2011 12:03 AM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] Recordset not updatable

So, this is the record source of frmJob which is being opening and an
attempt is made to move to a new record:
SELECT tblJobs.JobID, tblVehicles.VehicleID, tblVehicles.MakeID,
tblMakes.Make, tblVehicles.ModelID, tblModels.Model, tblVehicles.ModYear,
tblVehicles.Color, tblVehicles.VIN, tblVehicles.[Tag-State],
tblMasterCustomer.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
FROM tblModels INNER JOIN (tblMakes INNER JOIN (tblMasterCustomer INNER JOIN
(tblVehicles INNER JOIN tblJobs ON tblVehicles.VehicleID =
tblJobs.VehicleID) ON (tblMasterCustomer.CustomerID = tblJobs.CustomerID)
AND (tblMasterCustomer.CustomerID = tblVehicles.CustomerID)) ON
tblMakes.MakeID = tblVehicles.MakeID) ON tblModels.ModelID =
tblVehicles.ModelID;

If so, are all of the joins on primary and foreign keys? I'm not sure why
you have so many tables involved. Typically you would display the Make in a
combo box bound to MakeID and the same with Model and ModelID. I typically
have only 1 or 2 tables in my record sources with combo boxes and subforms
used to display the values from related tables.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: drtolson@verizon.net
Date: Sun, 11 Sep 2011 22:30:50 -0400
Subject: RE: [MS_AccessPros] Recordset not updatable

Thank you Duane, I see why you asked the question...Because I confused you
by referring incorrectly to the SQL that you asked about. The first SQL that
I identified below is from frmJob and displays fields relative to the
repair. The SQL that has the customer contact info - name, address, and
number - along with the make/model/year of vehicle that is being serviced is
in a third subform:

SELECT tblJobs.JobID, tblVehicles.VehicleID, tblVehicles.MakeID,
tblMakes.Make, tblVehicles.ModelID, tblModels.Model, tblVehicles.ModYear,
tblVehicles.Color, tblVehicles.VIN, tblVehicles.[Tag-State],
tblMasterCustomer.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
FROM tblModels INNER JOIN (tblMakes INNER JOIN (tblMasterCustomer INNER JOIN
(tblVehicles INNER JOIN tblJobs ON tblVehicles.VehicleID =
tblJobs.VehicleID) ON (tblMasterCustomer.CustomerID = tblJobs.CustomerID)
AND (tblMasterCustomer.CustomerID = tblVehicles.CustomerID)) ON
tblMakes.MakeID = tblVehicles.MakeID) ON tblModels.ModelID =
tblVehicles.ModelID;

So there is one Parent form with 3 child forms. I sure hope I can help you
help me :-).

r/David

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
Sent: Sunday, September 11, 2011 10:11 PM
To: Access Professionals Yahoo Group
Subject: 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



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

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

Yahoo! Groups Links

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.


A bad score is 598. A bad idea is not checking yours, at freecreditscore.com.
.

__,_._,___

Tidak ada komentar:

Posting Komentar