If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Cry For Help! - Filling In Forms Automatically From Tables
I am DESPERATE for a quick reply.. have exhausted my options...
i have a table called order details which contains: order id product id product name quantity unit price sale price discount and a products table containing: Product id product name unit price units in stock I have a form which currently shows text boxes for order id product id product name quantity unit price (product id is a combo box) i can choose the product id from the drop down i have created but it does not put any other data into any of the other fields.. how on earth EXACTLY can i manage to do this? i want the data from the product table to fill in the form once i choose the product id from the form combo box cheers for ANY help |
#2
|
|||
|
|||
Cry For Help! - Filling In Forms Automatically From Tables
If the Product Name will not change (always based on the Product ID) then
there is generally no good reason to save the Product Name in the details table. Do you have a good reason to save both the Product ID and Product Name in the details table? -- Duane Hookom MS Access MVP "-elniniol999-" wrote in message ... I am DESPERATE for a quick reply.. have exhausted my options... i have a table called order details which contains: order id product id product name quantity unit price sale price discount and a products table containing: Product id product name unit price units in stock I have a form which currently shows text boxes for order id product id product name quantity unit price (product id is a combo box) i can choose the product id from the drop down i have created but it does not put any other data into any of the other fields.. how on earth EXACTLY can i manage to do this? i want the data from the product table to fill in the form once i choose the product id from the form combo box cheers for ANY help |
#3
|
|||
|
|||
Cry For Help! - Filling In Forms Automatically From Tables
You have a recordsource for your combobox. Probably
something like "Select productId from tblProduct;" Change this to "Select ProductId, ProductName, UnitPrice from tblProduct;" Change the columnCount and columnwidth properties of the combobox to match. in the combobox click event put code like if cboProduct.listindex 0 then exit sub txtPName.value = cboproduct.column(1,cboproduct.listindex) txtUnitPrice.value = cboproduct.column (2,cboproduct.listindex) -----Original Message----- I am DESPERATE for a quick reply.. have exhausted my options... i have a table called order details which contains: order id product id product name quantity unit price sale price discount and a products table containing: Product id product name unit price units in stock I have a form which currently shows text boxes for order id product id product name quantity unit price (product id is a combo box) i can choose the product id from the drop down i have created but it does not put any other data into any of the other fields.. how on earth EXACTLY can i manage to do this? i want the data from the product table to fill in the form once i choose the product id from the form combo box cheers for ANY help . |
#4
|
|||
|
|||
Cry For Help! - Filling In Forms Automatically From Tables
Your tables are wrongly set up.
You need to have one table, which holds the order information, with the following fields: Order Table: ---------------- order id product id quantity and one table for the product information with the following fields: Product Table: ------------------ Product id product name unit price units in stock sale price discount Create a query from these two tables with: + order id, product id and quantity from the Order table, + then product name, unit price, units in stock, sale price and discount from the Product table. Use this query with all its fields as the basis for your form. Add the fields to your form respectively change the control source for existing fields to the query fields. Whenever you now select a product from your combo box (which should add its info into the 'product id' field), all other fields should update accordingly. "-elniniol999-" wrote in message ... I am DESPERATE for a quick reply.. have exhausted my options... i have a table called order details which contains: order id product id product name quantity unit price sale price discount and a products table containing: Product id product name unit price units in stock I have a form which currently shows text boxes for order id product id product name quantity unit price (product id is a combo box) i can choose the product id from the drop down i have created but it does not put any other data into any of the other fields.. how on earth EXACTLY can i manage to do this? i want the data from the product table to fill in the form once i choose the product id from the form combo box cheers for ANY help |
#5
|
|||
|
|||
Cry For Help! - Filling In Forms Automatically From Tables
"Sabine Oebbecke" wrote in message
om... Your tables are wrongly set up. You need to have one table, which holds the order information, with the following fields: Order Table: ---------------- order id product id quantity and one table for the product information with the following fields: Product Table: ------------------ Product id product name unit price units in stock sale price discount Create a query from these two tables with: + order id, product id and quantity from the Order table, + then product name, unit price, units in stock, sale price and discount from the Product table. Use this query with all its fields as the basis for your form. Add the fields to your form respectively change the control source for existing fields to the query fields. Whenever you now select a product from your combo box (which should add its info into the 'product id' field), all other fields should update accordingly. Sorry, but I strongly disagree with this setup. Both Sale Price and Discount are dynamic values that can (and likely will) change over time. With your method anyone looking at an Order from a year ago would see *current* price and discount values, not the values that were in effect at the time of the Order. This is a case where it is NOT a breach of normalization principles to copy the values from the Products table to the Orders table at the time the Order record is created so the OP was closer to doing it correctly. The only thing I would set up differently is that the Orders table does need to have both the ProductID and the ProductName. To the OP... Add all of the info you need to the ComboBox for ProductID as additional columns (they can be hidden if desired). Then in the AfterUpdtae event of the ComboBox you run code similar to... Me.Price = Me.ProductID.Column(1) Me.Discount = Me.ProductID.Column(2) etc.. I would NOT do the above for ProductName as this does not need to be stored with your Order. Just have an unbound TextBox with a ControlSource of... =ProductID.Column(3) ....where the fourth column of the ComboBox contains the ProductName from the Products table. This will allow the user to *see* the ProductName, but will not redundantly store it. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
Thread Tools | |
Display Modes | |
|
|