David-
It sounds like you need to rethink your form design to use forms and subforms.
One form might show customers on the outer form and vehicles they own on a
subform. Another form might show repair order on the outer form with vehicles
on the subform.
It would help to know how all these tables are related and the specific steps
you're trying to have the user go through to create a new repair order.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of djsdaddy531
Sent: Tuesday, September 27, 2011 7:32 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Record not being found by Access in table
I am building an application that will keep track of vehicles that have been
repaired at a automobile repair shop. When I attempt to create a new repair
order, I get the following error message:
Run-time error '3101':
The Microsoft Office Access database engine cannot find a record in the table
'tblVehicles' with key matching field(s) 'VehicleID'.
this is whether it is a repair order of a vehicle that has already been in the
shop for repair, or a new vehicle that has not been repaired before.
Here is the SQL from the form (frmRepairOrder-Select4Report2)that calls the
repair order form :
SELECT tblJobs.CustomerID, tblMasterCustomer.LastName,
tblMasterCustomer.FirstName, tblJobs.JobID, tblJobs.VehicleID,
tblVehicles.ModYear, tblVehicles.MakeID, tblMakes.Make, tblVehicles.ModelID,
tblModels.Model, tblJobs.RepairDateIn
FROM tblModels INNER JOIN (tblMakes INNER JOIN (tblMasterCustomer INNER JOIN
(tblVehicles INNER JOIN tblJobs ON tblVehicles.VehicleID = tblJobs.VehicleID) ON
tblMasterCustomer.CustomerID = tblJobs.CustomerID) ON tblMakes.MakeID =
tblVehicles.MakeID) ON tblModels.ModelID = tblVehicles.ModelID
ORDER BY tblMasterCustomer.LastName, tblMasterCustomer.FirstName;
and here is the SQL from the repair order form (frmJob):
SELECT tblJobs.JobID, tblJobs.CustomerID, tblJobs.VehicleID,
tblMasterCustomer.FirstName, tblMasterCustomer.LastName, [FirstName] & " " &
[LastName] AS FullName, tblMasterCustomer.StreetAddressLine1,
tblMasterCustomer.StreetAddressLine2, tblMasterCustomer.City,
tblMasterCustomer.State, tblMasterCustomer.ZipCode, [City] & ", " & [State] & "
" & [ZipCode] AS [City-State-Zip], tblMasterCustomer.HomePhone,
tblMasterCustomer.BusinessPhone, tblMasterCustomer.MobilePhone,
tblMasterCustomer.EmailAddress, tblJobs.JobTypeID, tblJobs.RepairDateIn,
tblJobs.RepairDateOut, tblJobs.WarrantyWorkPerformed,
tblJobs.DateOfWarrantyWork, tblJobs.DescribeWarrantyWork,
tblJobs.RepairMilageIn, tblJobs.RepairMileageOut, tblJobs.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;
Thank you in advance for your assistance.
r/David
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar