update a field in a table

[Walter]

I have a main form based on a trips table. On this form is a subform based
on a trip details table with a one to many relationship. There is also a
trucks table with a one to many relationship to the trips table. I have
several queries which run to validate various fields. This worked fine
originally but after several years of use and thousands of records it has
slowed drastically. On the "Trip End" record of the subform, I would like to
update the appropriate vehicle's record to store some of this information.
I've created an Update Query and used the DoCmd OpenQuery in the subform's
code to run it but it prompts for the data and then asks for verification to
run. Here is the query I have:

UPDATE tblTrucks INNER JOIN (tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID) ON tblTrucks.TruckID =
tblTrips.TruckID SET tblTrucks.LastOdometer = [forms]![frmTrips
Subform]![Odometer], tblTrucks.LastLocation = [forms]![frmTrips
Subform]![Location], tblTrucks.LastState = [forms]![frmTrips
Subform]![State], tblTrucks.LastTripDate = [forms]![frmTrips]![TripDate]
WHERE (((tblTrucks.TruckID)=[forms]![frmTrips]![TruckID]) AND
((tblTrips.TripID)=[forms]![frmTrips]![TripID]) AND
((tblTripDetails.TripDetailsID)=[forms]![frmTrips Subform]![TripDetailsID]));

Why does this not pick up the data from the record and is there a
better/easier way of doing this? Also, is there a way to turn off the
warning when this query runs without turning all warnings off?

 

[Lou]

I have a main form based on a trips table.  On this form is a subform based
on a trip details table with a one to many relationship.  There is alsoa
trucks table with a one to many relationship to the trips table.  I have
several queries which run to validate various fields.  This worked fine
originally but after several years of use and thousands of records it has
slowed drastically.  On the "Trip End" record of the subform, I would like to
update the appropriate vehicle's record to store some of this information..  
I've created an Update Query and used the DoCmd OpenQuery in the subform's
code to run it but it prompts for the data and then asks for verificationto
run.  Here is the query I have:

UPDATE tblTrucks INNER JOIN (tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID) ON tblTrucks.TruckID =
tblTrips.TruckID SET tblTrucks.LastOdometer = [forms]![frmTrips
Subform]![Odometer], tblTrucks.LastLocation = [forms]![frmTrips
Subform]![Location], tblTrucks.LastState = [forms]![frmTrips
Subform]![State], tblTrucks.LastTripDate = [forms]![frmTrips]![TripDate]
WHERE (((tblTrucks.TruckID)=[forms]![frmTrips]![TruckID]) AND
((tblTrips.TripID)=[forms]![frmTrips]![TripID]) AND
((tblTripDetails.TripDetailsID)=[forms]![frmTrips Subform]![TripDetailsID]));

Why does this not pick up the data from the record and is there a
better/easier way of doing this?  Also, is there a way to turn off the
warning when this query runs without turning all warnings off?

I don't wish to encourage copying data from the tblTrips table to the
tblTrucks table. However, the way to turn off "update warnings"
is ...

DoCmd.SetWarnings 0
DoCmd.OpenQuery
DoCmd.SetWarnings 1

You might consider partitioning your current year's data from your
previous year's data by inserting the older data into a
tblTripDetailsArchive table, then deleting those rows copied from the
tblTripDetails table.