This has to be simple to everyone but me. I have a database with an invoice that is very similar to the sample Northwinds database provided with Access 2002. I am trying to duplicate the invoice sub form by taking data from the product table and placing it into the order details table.
When I set up my sub form and show product fields it works fine. The problem is sometimes it is necessary to change the product price on that invoice only. If I use the price field from the products table the price is updated in the product table.
By using the price field on the order details, like Northwinds,I can change the item price without updating the price in the product table. What I cant comprehend is how do I get the price from the product table and insert it in to the order details table. I did try to use the lookup wizard on the product ID in the order details table and I compared the Northwinds relationships and queries with mine but still no luck.
I do not have access to nwind so I hope that this will help. On the subform I assume you have a combo box for selecting the product.
In this control have a column that includes price. On the AfterUpdate event set the product price into the price control. This way if the user needs to overwrite this amount, they can simply by typing in a new amount in the price control.
Remember that combobox column references are zero-based so
txtPrice = ctl.column(1) is refering to the second column.