Hi,

I have a parts table with a field that holds a vendor's name which is selected from a combo box on a parts form. I've just noticed that if a vendor name is changed on the vendor form the field in the table isn't updated. I now have to go through all parts to adjust any changes done to the vendor name. Is there a way I can automatically update the vendor name in the parts table if there is a change done to the Vendor name in the vendor table?

Thanks

In your table of vendor's you should have some kind of primary key, usually an autonumber field, along with a field containing the vendor's name.

Currently, you appear to be storing the name of the vendor directly into your parts table. You should be storing the PK field number corresponding to the vendor instead of the name itself.

Your combo box sql should have both fields, it can store the pk number but only show the field with the name in it. To do this you ensure that the sql gets both fields from your vendor table in the following order: PkField then VendorNamefield. Set the column count propert of the combo to 2, and set the column widths to 0",2". Make sure that the bound column property = 1.

Then, whenever you make an edit to a vendor name, your combo box will show that change wherever you reference that vendor's pk.

Thanks for the reply,
I've made a big boo boo, I've tried this but the field is set to text and when I change it to number all the data is deleted. Do you have any other suggestions?

Thanks for your advice, I got it to work.

Ok...hopefully you backed up your db first! If not, and you changed the data type of the text field despite the warning about data loss then I'm afraid the data is lost.

To make the transition per my original suggestion:

Backup your db.

Using your original Parts table, add a new number field called VendorID.

Then create a new update query.

Add the parts table and vendor table to the query. Link the vendor name fields in the two tables together. Add the VendorID field from the parts table to the query grid. In the Update to row put [YourvendortablenameHERE]![VendorID]. Then run the update query.

Check your parts table, you should see numbers that represent the pk associated with the vendor. Once you've confirmed this, then you no longer need the update query or the original text vendor name field and you can delete them.

In your relationships view, you simply join the Parts table to the Vendor table using the VendorID field.

You'll then need to redo the sql of your combo boxes.

Oh, never mind then :)