Update one field based on another field from another table.

[rbloomfield]

Hello,

I have a query called SData2. One of the fields is a part number which originally comes from a form called frmSData which has a part number list box that links to a table called PNumbers.

I'm trying to create a new query so that, depending on the part number, a field called PartPrice appears with the appropriate price for that part number which is also located in the PNumbers table.

So how do I get a query to automatically update the PartPrice field based on the PartPrice Field from the PNumbers table that matches the part number that is found in the SData2 query?

If you're not as confused as I am by now, please help.

 

[Trevor G]

Welcome to the Forum,

You can create an update query to achieve your goal. If you create a normal select query to start with then bring in the 2 tables and relate them.

Select the fields you need to compare, then add the criteria. Then adjust the Select query to an update query. I would suggest then pressing F1 and search the Help for Update query. You do get a reasonable example that you can work with.

Then it is just a case of running the update query from your form, I would create a macro to run the query, in the macro I would also look to set the warnings off whilst running otherwise you will see a message stating you are about to run an update query, but don't switch the warnings off before you have checked it works in the correct way.

I hope this helps you.