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
|
|||
|
|||
show 2nd field when combo box choice is made
Greetings:
I have a db for clients and their purchases. In my attempt to further normalize my tables, I have created a new table to list the products clients can purchase. So now i have 3 tables. One has the client info, the 2nd has the individual orders (ID, client foreign key, product (stores combo box info) , order date, quantity ordered and a price number field to hold the info this question is about) and the 3rd has the list of products and the price of each product. I have a form to enter the client info and a subform to enter each client's order info. The order subform has a combo box to choose the product ordered. When I choose the product,I would like the price from the product table to go into a field in the subform and be recored in the order table. Is this possible? Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201005/1 |
#2
|
|||
|
|||
show 2nd field when combo box choice is made
Set up your combobox using the wizard and include the fields you need, from
Left-to-Right. If in the Combobox they appear as Product Price the code would be Private Sub YourComboBoxName_AfterUpdate() Me.SubformPriceField = Me.YourComboBoxName.Column(1) End Sub The column index is zero based, so the first column would have an index of 0 (zero). You sound as if your combobox is bound so that the Product is saved directly to your underlying table, but if you wanted to assign the Product to a textbox, you would use Me.SubformProductField = Me.YourComboBoxName.Column(0) -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201005/1 |
#3
|
|||
|
|||
show 2nd field when combo box choice is made
In the after update event for the combo, you can use code to copy the price
from a hidden column of the combo to the price textbox. Me.PriceTextboxName = Me.ComboName.Column(2) For a sample database that shows this in action go to http://allenbrowne.com/TechniqueEnterCalcText.html Don't be put off by the title which is about entering text in calculated controls, this database does show how to get the price into the orders table. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Scott_Brasted via AccessMonster.com" u56211@uwe wrote in message news:a85a0a19e7f37@uwe... Greetings: I have a db for clients and their purchases. In my attempt to further normalize my tables, I have created a new table to list the products clients can purchase. So now i have 3 tables. One has the client info, the 2nd has the individual orders (ID, client foreign key, product (stores combo box info) , order date, quantity ordered and a price number field to hold the info this question is about) and the 3rd has the list of products and the price of each product. I have a form to enter the client info and a subform to enter each client's order info. The order subform has a combo box to choose the product ordered. When I choose the product,I would like the price from the product table to go into a field in the subform and be recored in the order table. Is this possible? Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201005/1 |
#4
|
|||
|
|||
show 2nd field when combo box choice is made
That's it exactly. I am trying to understand exactly how to do this. I will
re-read it a couple of times before I throw my hands up and ask for more help. Many thank to you both. Best, Scott Jeanette Cunningham wrote: In the after update event for the combo, you can use code to copy the price from a hidden column of the combo to the price textbox. Me.PriceTextboxName = Me.ComboName.Column(2) For a sample database that shows this in action go to http://allenbrowne.com/TechniqueEnterCalcText.html Don't be put off by the title which is about entering text in calculated controls, this database does show how to get the price into the orders table. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia Greetings: [quoted text clipped - 21 lines] Thanks, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201005/1 |
#5
|
|||
|
|||
show 2nd field when combo box choice is made
Scott:
The sample Northwind database illustrates another way of doing this by looking up the unit price from the Products table by means of the DLookup function. The code for this is in the AfterUpdate event procedure of the ProductID control on the Order Details subform. In earlier versions this is done directly in the event procedure, but in Access 2007 then code calls the GetListPrice function, which in turn calls the DLookupNumberWrapper function. Note that the Northwind database has Orders and Order Details tables, the latter modelling the many-to-many relationship between orders and Products. This allows each order to include more than one product. In your case you are allowing only one product per order, which may always be the case in your business model. If not you should decompose your Orders table along the lines of those in Northwind. Otherwise your Orders table is not fully normalized as it would contain redundancies in the event of one order covering more than one product. Ken Sheridan Stafford, England Scott_Brasted wrote: Greetings: I have a db for clients and their purchases. In my attempt to further normalize my tables, I have created a new table to list the products clients can purchase. So now i have 3 tables. One has the client info, the 2nd has the individual orders (ID, client foreign key, product (stores combo box info) , order date, quantity ordered and a price number field to hold the info this question is about) and the 3rd has the list of products and the price of each product. I have a form to enter the client info and a subform to enter each client's order info. The order subform has a combo box to choose the product ordered. When I choose the product,I would like the price from the product table to go into a field in the subform and be recored in the order table. Is this possible? Thanks, Scott -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|