Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2008
    Posts
    4

    Unanswered: List box population

    Hello all

    My problem is the following: (i use ms access)

    I have a table named ProductCategories and many other tables named Coffees, soft drinks, juices, Alcohol, etc etc
    The ProductCategories table contains the following values: coffees, soft drinks, juices etc,etc.
    As you understand each value of the ProductCategories table is a seperate table itself. The coffees tables contains: espresso, filter coffee, etc
    The soft drinks table contains: coca cola, orange juice, lemonade etc.

    Now, i have 2 listboxes. Listbox1 is populated with the data of the ProductCategories table. (coffees, juices, alcohol etc).
    So far ok.
    I want listbox2 to be populated with the data of each other product table.
    for example. I want to click on the coffee item in listbox1 and automatically the listbox2 to get populated with the data from the table "coffees".

    Or differently, when i select an item from listbox1, by clicking a button to populate listbox2 with the values from the relative product table.

    I know i must use the selecteditem methos but it gives me error.
    I ve searched the whole net but i cant find any helpful sites.

    Normally the pseudocode has to be like this:
    If listbox1.selecteditem = "coffees" then
    populate listbox2 with values from "coffees" table.

    I also know it can be done using, the case method.

    Any help would be much appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by kostello
    Hello all

    My problem is the following: (i use ms access)

    I have a table named ProductCategories and many other tables named Coffees, soft drinks, juices, Alcohol, etc etc
    not a good idea... a product is a product and unless you have dramtically different attributes for each prodcut type then they should all be in one table

    Quote Originally Posted by kostello
    The ProductCategories table contains the following values: coffees, soft drinks, juices etc,etc.
    As you understand each value of the ProductCategories table is a seperate table itself. The coffees tables contains: espresso, filter coffee, etc
    The soft drinks table contains: coca cola, orange juice, lemonade etc.

    Now, i have 2 listboxes. Listbox1 is populated with the data of the ProductCategories table. (coffees, juices, alcohol etc).
    So far ok.
    I want listbox2 to be populated with the data of each other product table.
    for example. I want to click on the coffee item in listbox1 and automatically the listbox2 to get populated with the data from the table "coffees".

    Or differently, when i select an item from listbox1, by clicking a button to populate listbox2 with the values from the relative product table.

    I know i must use the selecteditem methos but it gives me error.
    I ve searched the whole net but i cant find any helpful sites.

    Normally the pseudocode has to be like this:
    If listbox1.selecteditem = "coffees" then
    populate listbox2 with values from "coffees" table.

    I also know it can be done using, the case method.

    Any help would be much appreciated.
    if you had all products in one table and a RI Link / Foreign Key established between the category and the product then you could apply that filter to the second listbox.

    normally you'd do this by putting some code in the first listboxes 'on click' event, which sets either a filter or a new SQL recordsource for the second listbox based on the choice in the first list box

    incidentally you may choose to make the rowsource for the first list box to be set manually offering an option of all items.
    you could choose instead of a list box to have a tree hierarchy

    you could choose to have a hierarchy in the product types
    eg
    TypeID Description ParentID
    1 beverages
    2 snack
    30 junk food
    10 cold drinks 1
    4 hot drinks 1
    5 Fruit Juices 10
    100 Freshly Squeezed 5
    101 Fresh Cartons 5
    102 Canned 5
    103 Long Life Carton 5
    11 Orange 103
    12 Grapefruit 103
    13 Ruby Grapefruit 12
    ...etc

    Rudy (R937) has a good reference tucked away somewhere but I seem to have lost the reference to it. there are many examples on t'net look for the adjaceny model
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The aforementioned reference:

    http://www.r937.com/Relational.html
    Paul

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    One thing to keep in mind with listboxes is that there are two ways to select items. One is mulit-select and the other is single selection. If your listbox is single selection then you need to get the item from the Value property. If it is multiselect then it will be in the ItemsSelected collection. In your case I suspect you want:

    If listbox1.Value= "coffees" then

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pbaldy
    The aforementioned reference:

    http://www.r937.com/Relational.html

    as useful a reference that is, and i think it is applicable for the OP, the reference I'm referring to is on the adjacancey model of gettign hierarchies out of a single table.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2008
    Posts
    4
    Quote Originally Posted by DCKunkle
    One thing to keep in mind with listboxes is that there are two ways to select items. One is mulit-select and the other is single selection. If your listbox is single selection then you need to get the item from the Value property. If it is multiselect then it will be in the ItemsSelected collection. In your case I suspect you want:

    If listbox1.Value= "coffees" then
    Thats what i want.
    But after then...?
    How can i say that i want the second listbox to list the values of the selected table? table "coffees" for example?

    Thank you very much...

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    take a step out and consider the comments about normalising your design. Right now in my books its seriously flawed, and flawed in a mannner that makes your application needlessly complicated and a pig to maintain.

    waht you are looking for, as advised earlier is to place some code in the category listbox which changes the recordsource in the second list box. the required code is placed in listbox1's on click event... ie when an item is selected in listbox1 you then enact some code which changes listbox2 recordsource

    the code will be somehting like
    listbox2.rowsource = "select My,column,list from mytable order by a,column,order"
    listbox2.requery

    i would expect a where clause to be inserted in the between the 'mytable' and 'order by'

    because of your current design you'd probably want
    select case listbox1.value
    case "Coffee"
    strSQL="select my,column,list from Coffee order by a,column,list"
    case "Soda"
    strSQL="select................
    end select
    listbbox2.rowsource=strSQL
    listbox2.requery

    to be honest Im not certain the code is right (Im not using a computer with Access on and my memory is failing)... you will need to play about with it it may be recordsource
    you may not need a requery

    personally I'd redesign the product tables into a single product table
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2008
    Posts
    4
    Thank you very much for your help.
    I solved my problem
    Now i continue with my database...

    I want to do the following:

    After chosen a product from listbox2, i would like to have 2 buttons which add and delete a product in/from the relative table.

    I may need your help...

    CHeers

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    new question, new thread

    however rather than just go ahead an ask a question can I suggest you make an effort to find a soultion on your own... why you may ask.... I'd argue that you are going to learn more from the process if you make an effort yourself, learn a bit about what is going on in an Access applciation, learn to use some of the tools that come with Access
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2008
    Posts
    4
    I didnt ask for a solution for my next step.
    I just said what i am planning to do in terms of "chatting" about my application.

    And who told you that i don't try to find a solution myself?
    Before asking, i was struggling for many days. Finally i decided to ask for help because the project would stay back.

    I suggest you and all other members, to encourage new developers and new members in here and help each other.

    That's why you are a member in here and that's why this forum exists.

Posting Permissions

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