Auto Fill Fields with data from other tabel

[Marinus]

Actually, I've just had another look at that query, and it's going to update the incorrect table (my bad ). However given that you are already storing the MaterialsID in your Docket Table, you really don't need to store prices as well. You can simply collect the price based on the materialsID you are already storing in the docket table.

And this is where the problem is, the MaterialsID is not stored in the docket and that wouldn't be handy because I would always have the latest prices in the MaterialsDB. but The crux is that I need the Values of Materials!BuyPrice and Materials!SalesPrice stored in Docket!BuyPrice and Docket!SalesPrice so they reflect the value for the materials at that moment. If the prices in the MaterialsDB are changed these prices should be written to the new records created..

When I now run QRY_PriceUpdate, I get a message box asking for BuyPrice and SalesPrice but then the Prices in the materials database are updated..
One good thing it does it at the right place. So fired from a double click event, I would not have to open the table to edit the price of a material.

This leaves open the fact that when I open the DocketTable none of my BuyPrice or SalesPrice is filled with prices from the MaterialsTable, not in the old records and not in new records created..

As far as I know, storing the ID will only give me the current prices, this is why I need to store the prices itself in the table.. A fact is that the operator of this Touchscreen does not know the prices so the fields will be hidden when everything works,,

 

[John Big Booty]

......

Once a record is created they should stay there, if a week later the price is changed in the material db I need to write the new prices in these fields so each record has the buyprice or salesprice for that moment.. Again, you all have been a great help and I hope I explained this function better now..

OK that makes sense

 

[Marinus]

OK that makes sense

Sorry, very Dutch and my English is not so good, easy cause for misunderstandings.. Furthermore, this is only my second attempt at programming in 20 years and there is so much that I forgot or is changed..
So hope you Guy's can help with this problems..

As far as the QRY_Update this is not obsolete as I can use this for the administrator to change the pricing on one material without opening the table. I find this a very neat solution in the admins functionality..

 

[Marinus]

Thanks for all your input Guys, Didn't sleep all night but I am sure you are all accustomed with this phenomena. Did sort with Update Query as suggested.
Took a while until I got the hang of it..

So here is the SQL code;

[COLOR="Red"]UPDATE Materials INNER JOIN Docket ON Materials.ID = Docket.Materials SET Docket.BuyPrice = [Materials].[BuyPrice], Docket.SalePrice = [Materials].[SalesPrice]
WHERE (((Docket.ID)=[Forms]![Touchscreen]![List210]) AND ((Materials.ID)=[Forms]![Touchscreen]![List125]));[/COLOR]

Works a treat, now up to finding out where to fire, any suggestions welcome as this would grand me a nights sleep as reward..

P.S. Couldn't have done without all ye guys help, AND the book you suggested Bob.. It's great