Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Aug 2005
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    access: combo box / text field problem

    HI,

    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
    Price (textfield)
    Total

    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:
    Code:
    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....
    Last edited by urko; 09-30-2006 at 06:51 PM.

  • #2
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    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:

    Code:
    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.

    Code:
    Private Sub IDKat_AfterUpdate()
      [Product].RowSource = "SELECT DISTINCT Products.ID, Products.Name, Products.Price FROM Products WHERE Products.IDKat=" & [IDKat] & " ORDER BY Products.Name"
    End Sub
    
    Private Sub Product_AfterUpdate()
      [Price] = [Product].Column(2)
    End Sub
    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.

    -Shane
    Last edited by TheShaner; 10-24-2006 at 05:16 PM.

  • #3
    New to the CF scene
    Join Date
    Nov 2006
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Having the same problem....

    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! 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!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •