| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > Populate field in subform using combo box
Using MS Access - Populate field in subform using combo box
Expert: Scottgem - 11/5/2009
Question
QUESTION: Hello, I am trying to redesign a current database using MS Access 2007. I have data from 3 tables (Categories, Products, Transactions). In the subform, I want it to be able to select CatName from Categories using a combo box, then next field (also a combo box) will only show products that are connected to selected CatName. Lastly, after products were selected, it will automatically fill in the fields in the datasheet.
How do I do that? Thanks.
ANSWER: This is a standard technique called synchronized or cascading combos. This article:
http://support.microsoft.com/kb/289670
shows how to do it. While this article is for Access 2003, the same techniques apply to 2007.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: Thank you for your help, I am able to figure out how to sync the combo boxes, but What I am trying to do is: Select Category, then show all the Products in the category, then select the product, which after the product combobox update, will show the Units, and Quantity text boxes. I tried using a query on the control sources for Units and Quality, but I guess I did it wrongly. Any suggestion please?
Thank you again Scott.
Answer
I can understand showing units, but wouldn't the user be entering the quantity?
I'm assuming here that you are doing an Order entry system. And that your Transactions table records the details of the order. I'm also going to assume that the transactions table looks like this:
TransactionID (PK Autonumber)
ProductID
OrderID (FK)
UnitCost
Quantity
Generally you would include ONLY the Foreign Key linking to your products table since your data is in that table. You can get the Units from the product record. The exception to this rule is time sensitive data, like UnitCost. Since this value can change over time you would want to freeze it at the time the record is created.
So the RowSource of your Products combo should look like this:
SELECT ProductID, ProductName, UnitCost, Units
FROM Products
ORDER BY ProductName;
To display the Units you would set the controlsource of the units textbox control to:
=Products.Column(3)
this will allow the text box to display the value in the 4th column (column count starts at 0) of the combo. However, to get the Unitcost you would use the After Update event to run this line of code:
Me.txtUnitCost = Me.products.column(2)
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
Add to this Answer Ask a Question
|
|
