Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    29

    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:

    UPDATE people INNER JOIN Vehicles ON people.ID = Vehicles.ID SET Vehicles.ID = [people].[id], Vehicles.[Last] = [people].[last], Vehicles.[First] = [people].[first]
    WHERE (((people.ID)=[Enter New Owner]) AND ((Vehicles.UniqueVehicleID)=[Enter Vehicle ID]));

    The problem is that it does not update the record in the vehicle table. I get "you are about to update no records". Is it because of the foreign key?
    Last edited by Rayce; 05-30-12 at 22:51. Reason: new info

  2. #2
    Join Date
    Feb 2012
    Posts
    29
    Bumping this. Still looking for assistance.

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    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.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Feb 2012
    Posts
    29
    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.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Okay, try converting the UPDATE statement into a SELECT one and seeing what results you get:
    Code:
    SELECT *
    FROM people INNER JOIN vehicles ON people.ID = vehicles.ID
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •