Amending Lookup Values

[DJ100]

I'm really new to this and I am struggling!

I have set up a database and am attempting to operate an ordering system through it. I have recently encountered a problem. I am attempting to bring prices from a list in a stock item table into the order table (via an order form) but sometimes I need to change the price as some vary based on labour charges, materials etc. I can bring the list price in but if I change it it changes the price in the original table, this is not what I require. I need to be able to change the list price so it is recorded in the order table, but remains the same in the stock item table.

Has anybody got any ideas?

I have been struggling on this problem for days - Help me PLEASE!

 

[Ken Grubb]

Sounds like the Control Source on your TextBox is set to the UnitPrice in your StockTable. If so, then yes, changes to the TextBox will update your StockTable. You'll need to use VBA in the form's code module to SELECT the appropriate record from your StockTable, display it in your TextBoxes (or other form controls), and store data in your OrderTable based upon the values displayed in the form. This way, there's a disconnection, if you will, between the StockTable (which should not change when processing Orders) and the values on your form.

HTH, but if not, please post additional info noting where I've presumed incorrectly.

 

[Pat Hartman]

You'll need to add the price field to your order details table. Use a query to populate the combo that is used to select a product. Include the product price as the third field (following the productID and ProductDescription). In the AfterUpdate event of the combo place code similar to the following:

Me.txtPrice = Me.cmbProduct.Column(2)

Me.txtPrice is the control name of the price field of your orderdetails table.
Me.cmbProduct is the name of the combo used to select the product - .Column(2) refers to the third column of the combo's recordsource which should be price.

This way, when a product is selected, the price field is populated, but you can override it if necessary.