Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2013
    Posts
    72

    Question Unanswered: Display data based on selection

    I got a question if you dont mind me asking


    I got got a PRODUCT table and created a SALES FORM

    On the salesForm I created a combobox which looks up in Product table and shows all product available. (tho' there are 3 types of tyres: Winter / Summer / Combo)

    Now I was thinking to make 2 drop comboboxes
    which lets u choose / specify

    - Tyre SIZE
    - Tyre Type

    Now, the next (drop down box) should only display the available items for that particular chocen tyre size and tyre type

    at minute it shows all of them ...
    it's like wanting to show all the tyres ONLY for size 16" Winter Tyres (eg)

    Any ideas?? Would anyone be able to assist??
    I am not that clever with VB and stuff .. dont dive into heavy stuff right away lol


    cheers

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is called "cascading comboboxes". There are plenty of examples here in the code bank (http://www.dbforums.com/microsoft-ac...code-bank.html) as well as on many other sites.

    The principle is that the RowSource property of a dependent (slave) combo changes with the selected item in the master combo. This is done in the AfterUpdate event of the maste combo.
    Have a nice day!

  3. #3
    Join Date
    Apr 2013
    Posts
    72
    Cheers SinnDho

    I will look into this and sure will ask for further help if I get stuck.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Apr 2013
    Posts
    72
    Sinn

    I managed to get this cascade combo up and running
    I got a tinny problem ...

    The second combobox, has 3 categories - Combo Tyre & Summer Tyre and Winter Tyre
    The 3rd combobox shows the items with its ID, name , stock and the stock remaining

    This is what I do not understand!

    Why, when I connect the product table with the sales table, does the 3rd combobox
    show me an item TWICE if it has two sales of same item.

    Example if the tyre 16" has been sold 2 times (one on Monday and one on Tuesday)
    then the query for some reason, shows the result on the 3rd combobox as 2 different items
    tho it is same item but sold twice ... ideally it should show one row but with the remaining stock.

    hmmm it's irritating

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please post the SQL statements used as RowSource for the comboboxes.
    Have a nice day!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Sinndho View Post
    Please post the SQL statements used as RowSource for the comboboxes.
    ...and your tables design,please
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2013
    Posts
    72
    Hi

    Code:
    SELECT Products.ProductID, Products.ProductProfile, Products.ProductName, Products.ProductPrice, Products.ProductStock, [ProductStock]-[SoldQuantity] AS Remain FROM Products INNER JOIN Sales ON Products.ProductID=Sales.ProductID WHERE (((Products.ProductSize)=forms![SALES ORDER]!cboSize) And ((Products.ProductType)=forms![SALES ORDER]!cboType));
    See attached files
    Attached Thumbnails Attached Thumbnails DB1.jpg   DB2.jpg  

  9. #9
    Join Date
    Apr 2013
    Posts
    72
    Also

    This is the strange part, as described above.
    If the same item has been sold 2twice, it shows it twice lol

    And if NO sale of the particular item has been made
    then the combobox shows blank

    see the pictures attached
    Attached Thumbnails Attached Thumbnails DB3.jpg   DB4.jpg  

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Why do you believe you need to join to the sakes table if you are searching for stock?

    For a search combo box you should be using unbound controls. If you stick with the normal design paradigm those unbound controls should be in either the forms footer or header.

    Potentially you have got at least 5 parameters
    Tyre type
    Rim size
    Width
    profile
    Speed rating
    Any change to any of those parametrs should change your target combo.
    Last edited by healdem; 02-03-14 at 12:31.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2013
    Posts
    72
    Healdem

    Why I joined the sales table??
    Because I wanted to show the "remaining [ProductStock] - [SoldQuantity]
    for each of the item .... just a reminder of how many are left.

    It works perfect without the sales table
    It only messes up once I join the sales table.

    Besides, not much of an DB geek and not sure what you said
    regards the parameters and header and footer.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Free stock is the sum of deliveries - sales. Use the sum function

    Then when it comes to showing tyres for sale you want a free stock which is greater than 0, and arguably equal to ir greater than the quantity required.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Apr 2013
    Posts
    72
    Quote Originally Posted by healdem View Post
    Free stock is the sum of deliveries - sales. Use the sum function

    Then when it comes to showing tyres for sale you want a free stock which is greater than 0, and arguably equal to ir greater than the quantity required.
    Sorry but I did not understand what you meant

Posting Permissions

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