Re: update field
Originally posted by nicky w
I want the unit price field in my order details to automatically update when I specify the product in the product ID field. My product table contains a list of my products and their unit prices. How do I do this? I saw that the northwind database uses some code for the afterupdate event on the productID but I dont fully understand it.
There are multiple ways to do this. The code that you would have seen would have been this:
strFilter = "ProductID = " & me!ProductID
This line of code tell the database which product ID you are looking for. In this case the product id is the value entered in the field productID on the form.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)
This simply says in the filed called UnitPrice in this Form (Me stands for active form) enter into it the result that DLookup gets.
THe DLookup function takes the name of the field from which you want the result , in this case "UnitPrice", the table where the field is, in this case "Products" and the which Product ID. That is defined by the filter created in the first line of code.
And easier method would be ....
When you are building the combobox, make sure that you select ProductID, ProductName, UnitPrice From the products table. Then set the Column Count property of the combobox to 3 and set the columns widths property to 0cm;3cm;0cm, therefore the combobox will only display the product name and hide the other two.
In the AfterUPdate evet of the combobox add this ine of code:
UnitPrice = ProductID.Column(2)
This will enter the content of the third column (Unit Price) into the Unit price field. Column count starts at 0.
Hope that this made sense.
Life is too short to be sane or sensible. Weird people rule and normal people suck