Im building a small script for myself and have one problem:
in Access - main form i have a subform that include following fields:
Category (ComboBox) - Independant
Product (ComboBox) - Dependent
I would like that when you select lets say category1 from Combo box - Category,
that in Product combo box only products that are under that category would be displayed.
After I would select the item from combo box 2 (Products) i want if it's possible to add the price to textfield (Price) automaticly.
This is the part of the code for combo box( Products) that i wrote but i always get items from category 1:
SELECT DISTINCT ARTIKLI.ID, ARTIKLI.Name, FROM Products WHERE (((Products.IDKat)=[SubFormOrder]!IDKat)) ORDER BY Products.Name;
IDKat is the name/source of combobox 1 (Category (ComboBox)).
I hope you know what i mean....
10-24-2006, 06:12 PM
I know this is an old post, but i figured i'd reply in case anyone has a similar problem.
For the products drop down, the rowsource query should look like:
SELECT DISTINCT Products.ID, Products.Name, Products.Price FROM Products WHERE Products.IDKat=[Forms]![SubFormOrder]![IDKat] ORDER BY Products.Name;
You had ARTKILI in front of ID and Name, so not sure if that's the real name of the table or not, but check that out. I included the Forms tag in the WHERE clause. I also included the Price for the product.
Now, on the AfterUpdate event for your IDKat combo box, you should put in [Product].Requery. That way, it refreshes the dropdown list to match the category selection.
Another way which may be even better (and which I use most) is actually changing the Product's rowsource query in the AfterUpdate event of the IDKat combo box.
Private Sub IDKat_AfterUpdate()
[Product].RowSource = "SELECT DISTINCT Products.ID, Products.Name, Products.Price FROM Products WHERE Products.IDKat=" & [IDKat] & " ORDER BY Products.Name"
Private Sub Product_AfterUpdate()
[Price] = [Product].Column(2)
You could also put "=[Product].Column(2)" in your DataSource for the Price text box, but if you're attempting to store that in a field for that form, then you'll have to go with the method in the code.
11-09-2006, 10:05 AM
I'm having the same problem... I haven't really used very advanced features of Access... so I'm not sure WHERE you guys are putting this code. Or how it works.
I've been asked to build a database at work, but two of the fields need to be dependent.
I have one table called "tblIdeas" which has the following fields: IdeaID (autonumber), Date (date), Name (text), Idea (memo). Somehow (whether it is in the same table or not, I do not know) I need to attach a Category and a dependent SubCategory to that 'idea'.
Someone has an idea about improving our telephone system, for example. They would fill out the date, their name, the category (which would be IT), and the subcategory (which would be dependent on what was chosen as the category - telephone system) and write their idea in a memo box.
I am having trouble understanding how I make that work! :confused: The only other option I have is to alphabetically sort the categories and put the subcategories in one field so that they can be found easily. Example:
Communications - Annual Report
Communications - Donations
Communications - General
IT - Computer Hardware
IT - Computer Software
IT - General
IT - Security
IT - Telephone System
...but I don't want to do that. It may suffice....
I would really appreciate a wiz to step me through the process.......
Thanks for your help! :thumbsup: