If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: updating a record from two open forms?

Database is essentially to track auto collectors and their vehicles. Primary tables are 'People' & 'Vehicles'. Vehicles has its own key and a foreign key from People. Foreign key is what ties each vehicle to it's owner. Multiple Vehicles per owner, single owner per vehicle.
Employer wants to move a vehicle from one owner to another. A form (ViewVehicles) with the specific vehicle record is open. I added a button to open another from (MoveVehToNewOwner)with the new owners info (for confirmation).
What I need to do is to assign the first 3 fields from the new owner to the vehicle that is open in the 1st form and update the record in the Vehicles Table.
The DB is

I am sure I am going about this all wrong. I would think an update query would work if I knew how to capture the date from the two forms.

My VB is very weak and I am working mostly from basic Access (2003) skills, wizards and help from this forum. I would appreciate any input or requests for more specifics.

Essentially I guess I want to update a record in the Vehicles table using 3 fields from the People table and the key from vehicles table by using data captured in two open forms. Sounds like going around my behind to get to my elbow. Not sure how to approach this. Any suggestion is appreciated.

10 minutes later.
I decided to create an update query to move the data requiring the user to enter the unique (new) owner ID and the unique vehicle ID. both are respective fields keys. owner ID is foreign key in vehicles table. created the query in design view but here is the SQL from the query:

It would help if you posted the structure of the tables, but it looks likes you're joining the tables together on their respective primary keys, which seems wrong to me.

Personally, I would favour using a third table to join the two together, one that included dates as well. This would also allow you to track the history of who was responsible for a vehicle at which times.

The (one-to-many) join is via the primary key of the People table and the foreign key of the Vehicle table.
I'm not at liberty to change the structure of the DB overall. My difficulty is in updating the vehicle table with the 3 pieces of the new owner info from the People table.