How can I update one table with values from another

[Ben2k]

Okay, I have two tables,

Cars
VsVehicles

Cars has various fields but the two we are interested in are [Stock Number] and [Chassis]

VsVehicles also has a lot of fields including [ID] and [VIN]

What I want to do is hit a button and access goes through the cars table and posts [Cars.Chassis] into the [VsVehicles.VIN] field on the vehicles table.

Can anyone help me?

Ben

 

[Ben2k]

Forgot to say the linked fields are [stock number] and [id]

 

[Andrew Fergus]

Hi Ben2k,

Create a new query, add the two tables (with the link), Query -> Update.

Field 1 of the query = VIN from the VsVehicles table
In the "Update To" section, enter [Chassis]
In the Criteria section, enter Is Null

Save and Run

Late edit : and if you want to run this when the user presses a button, you can create a "button" on your form, and via a macro set this query to run when the button is clicked.

Note : this assumes you want to update the VIN number for vehicles that don't have one, as opposed to changing the VIN for all vehicles. If you do want to change the VIN for all vehicles then remove the "Is Null" part.

HTH, Andrew.

 

[SydneyGeek]

The best way to do this is with an Update query.
1. Build a query with the 2 tables. Create a link on the table schemas from ID to Stock Number. Place the ID, Chassis and VIN fields in the grid.
2. Still in design view, select Update Query from the Query menu.
3. A new row appears in the grid. In the Update cell for [VIN], type [Chassis]
To run, hit the Run (exclamation mark) button.
Save the query.

In future, double-click the query icon to run the update. Say YES when Access asks you about modifying records.

Denis

 

[Ben2k]

Thanks

When I run it I get an error message

"Record(s) cannot be edited; no update permissions on vsvehicles"

I should have said that vsvehicles is a linked table from another database, If I open that database directly I can edit the table no problem.

How can I fix this?

Ben