Results 1 to 13 of 13
  1. #1
    Join Date
    May 2009
    Posts
    48

    Unanswered: Cascade Combo boxes and a List box

    Hello all. I am have a subform that uses a combo box to select a product and repair action from a list, which is then saved to a row in a table. If I need 5 repair actions, I have to go to each row on a subform and make the selection so I am doing this 5 times.

    The repair actions are priced differently for various years for each product so teh same repair actions are duplicated for each year. I want to be able to select an product from a combo box or list box which will populate another combo/list box with just the available repair years for the selected product. Selecting this will populate a multi-select list box where the user can select multiple repair actions that will then be saved in a table. If I select 5 repair actions in the list box, I need these 5 selections to pupulate 5 rows in the subform with the applicable product, repair year, and repair actions.

    Can anyone help with this?

    Thanks,
    Andrew

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by drewship View Post
    Hello all. I am have a subform that uses a combo box to select a product and repair action from a list, which is then saved to a row in a table. If I need 5 repair actions, I have to go to each row on a subform and make the selection so I am doing this 5 times.

    The repair actions are priced differently for various years for each product so teh same repair actions are duplicated for each year. I want to be able to select an product from a combo box or list box which will populate another combo/list box with just the available repair years for the selected product. Selecting this will populate a multi-select list box where the user can select multiple repair actions that will then be saved in a table. If I select 5 repair actions in the list box, I need these 5 selections to pupulate 5 rows in the subform with the applicable product, repair year, and repair actions.

    Can anyone help with this?

    Thanks,
    Andrew
    Andrew,

    The method you have describe may be possible with enough time and VBA coding.


    Using this method is standard:

    I am have a subform that uses a combo box to select a product and repair action from a list, which is then saved to a row in a table. If I need 5 repair actions, I have to go to each row on a subform and make the selection so I am doing this 5 times.
    I see the issue is in the way you handle the prices changes.

    The repair actions are priced differently for various years for each product so teh same repair actions are duplicated for each year.
    There are many issues with this way of handling price changes.Some are: There are lots or room for errors. Reporting is more difficult.

    Alternative solution:

    The way I handle this is to a price schedule, in your case by date, for each product.

    When you select an item in the combo box, in the after update event you look up up the current price using the sale/order date. I normally also stored the price with the item in the record so that it does not accidentally get changed very easily.

    If you want to stick with using the multi-select list box, which will add records, how will you handle editing?
    Last edited by HiTechCoach; 06-08-10 at 00:23.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    May 2009
    Posts
    48
    HiTechCoach, I have just finished cleaning a copy of my database and have attached it.

    If you click on Product in the subform, you will see the list of products and associated details. I want to break up the Product, Option Period, and Service Type into cascading combo/select boxes.

    Thanks,
    Andrew
    Attached Files Attached Files

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by drewship View Post
    HiTechCoach, I have just finished cleaning a copy of my database and have attached it.

    If you click on Product in the subform, you will see the list of products and associated details. I want to break up the Product, Option Period, and Service Type into cascading combo/select boxes.

    Thanks,
    Andrew
    Andrew,

    Unforutunately, cascading combo/list boxes are a lot more difficult to use on a form in continuous or datasheet mode.


    Here is an example:

    Cascading Combo Boxes Database
    Demonstrates how to make combo boxes whose values are filtered by the value of other combo boxes in a cascade.
    Hope this helps ...
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    May 2009
    Posts
    48
    Thanks HiTechCoach. I will look at the database and see if it is something I can incorporate or if it would be better to redesign my database to not use the continous subform.

    Andrew

  6. #6
    Join Date
    May 2009
    Posts
    48
    The code in your example populates the combo boxes from a subform. I would like to use the Products table in my database. What code changes do I need to select Products.ProductName from the first combo box and Products.RepairPeriod from the second combo box? The third box I would like to change to a multi-select list box so multiple rows can be selected to save to the database.

    Thanks,
    Andrew

  7. #7
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by drewship View Post
    The code in your example populates the combo boxes from a subform. I would like to use the Products table in my database. What code changes do I need to select Products.ProductName from the first combo box and Products.RepairPeriod from the second combo box?
    The third box I would like to change to a multi-select list box so multiple rows can be selected to save to the database.

    Thanks,
    Andrew
    Andrew,

    Unfortunately, a .MDB format does not support "a multi-select list box so multiple rows can be selected to save to the database". The ability to save multiple values in a single field was added to the new Access 2007/2010 format (.accdb).

    After re-reading your original post, where you want the multi-select list box to be used to create multiple records, I am not sure how well that would work within a sub form. I would suggest that you use a separate pop-up form for adding multiple records. That would keep the UI cleaner.

    So you did you decide NOT to use a price schedule so that you only have to enter a item once and the correct price can be automatically select?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  8. #8
    Join Date
    May 2009
    Posts
    48
    Thanks. I still need to use the price schedule, but I was hoping that I can use 2 combo boxes to narrow down the choices, then use a multiselect list box to select the actual repairs to be performed.

    I found the following from Microsoft and have tried to tailor it to my Products table. It gives me a pop window instead of displaying the Product list.

    "Open the sample database Northwind.mdb.
    Create a new form that is not based on any table or query with the following combo boxes, and then save the form as Categories And Products.
    Combo Box 1
    -------------------------------
    Name: Categories
    RowSourceType: Table/Query
    RowSource: Categories
    ColumnCount: 2
    ColumnWidths: 0";1"
    BoundColumn: 1
    AfterUpdate: [Event Procedure]

    Combo Box 2
    --------------------------
    Name: Products
    RowSourceType: Table/Query
    ColumnWidths: 2"
    Width: 2"


    NOTE: If you are in an Access project, the RowSourceType will be Table/View/StoredProc.
    Add the following code to the AfterUpdate event procedure of the Categories combo box:
    Me.Products.RowSource = "SELECT ProductName FROM" & _
    " Products WHERE CategoryID = " & Me.Categories & _
    " ORDER BY ProductName"
    Me.Products = Me.Products.ItemData(0)


    View the Categories And Products form in Form view. Note that when you select a category in the first combo box, the second combo box is updated to list only the available products for the selected category."

    Code:
    Private Sub cboProducts_AfterUpdate()
    Me.cboOptionPeriod.RowSource = "SELECT ProductID FROM" & _
       " Products WHERE ProductName = " & Me.cboProducts & _
       " ORDER BY ProductName"
    Me.cboOptionPeriod = Me.cboOptionPeriod.ItemData(0)
    End Sub
    This looks like it will narrow down the options if the user selects the Product from the Products dropdown, then selects the desired repair period from the Option Period dropdown. The Products dropdown shows the ProductID instead of the actual Product name, and when selected, displays a popup asking for the input.

    I will keep fooling with it. Hopefull I can figure it out.

    Andrew

  9. #9
    Join Date
    May 2010
    Posts
    601
    Normally you do use the Primary key (ID) field to filer with.

    When using string as criteria you must wrap then with quotes.

    Code:
    Private Sub cboProducts_AfterUpdate()
    Me.cboOptionPeriod.RowSource = "SELECT ProductID FROM" & _
       " Products WHERE ProductName = " & Chr(34) & Me.cboProducts & Chr(34) & _
       " ORDER BY ProductName"
    Me.cboOptionPeriod = Me.cboOptionPeriod.ItemData(0)
    End Sub
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  10. #10
    Join Date
    May 2009
    Posts
    48
    Ok. I tried the Chr(34) but it did not really do anything that I could tell. I have uploaded a new database with 2 combo boxes in the middle (Products and Repair Period) and a multiple list box (Repair Action). I tried to get the Products combobox to list the DISTINCT products which should only display a single Apple and a single Orange as options, but I still get duplicates.

    This does populate the Repair Period combo box...but only with a single item that isn't even a valid Repair Period. Repair Periods are numerical, listed in the Products table in column 4 (RepairPeriod), and should be listed as 1 through 4 a single time in the combo box with their BeginDate and EndDate.

    Once the Repair Period is selected, that value should populate the list box with all the Repair Actions that match both the Product and Repair Period selected in the combo boxes. It is here that the user should be able to select mulitple Repair Actions to be saved to the current record. The Service Type and Unit Cost should be displayed here so the user knows what to select and how much it will cost.

    I hope I have explained this well enough and appreciate any assistance with this. I am not well versed enough in SQL or VB to figure this out on my own.

    Andrew
    Attached Files Attached Files

  11. #11
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by drewship View Post
    Ok. I tried the Chr(34) but it did not really do anything that I could tell. I have uploaded a new database with 2 combo boxes in the middle (Products and Repair Period) and a multiple list box (Repair Action). I tried to get the Products combobox to list the DISTINCT products which should only display a single Apple and a single Orange as options, but I still get duplicates.
    I would suggest creating a saved query that you use as the combo box's row source. It is current a table which shows everything.

    Quote Originally Posted by drewship View Post
    This does populate the Repair Period combo box...but only with a single item that isn't even a valid Repair Period.
    That is because of this code:

    Code:
    Private Sub cboProducts_AfterUpdate()
    Me.cboRepairPeriod.RowSource = "SELECT DISTINCT ProductName FROM" & _
       " Products WHERE ProductID = " & Me.cboProducts & _
       " ORDER BY ProductName"
    Me.cboRepairPeriod = Me.cboRepairPeriod.ItemData(0)
    End Sub
    It looks like you are setting the cboRepairPeriod.RowSource to be what the should be for the cboProducts.RowSource

    Note: If you will use my recommendation for each product to be entered in the products table once, it will eliminate most of the issues you are working so hard to handle.

    I think I understand what you are trying to do, I just don't understand why. What you are doing may work, but in my opinion it is not using a properly normalized table design. Because of your design, it is forcing you to make this a lot more difficult than needed.


    Is there some reason that you must have all the products duplicated?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  12. #12
    Join Date
    May 2009
    Posts
    48
    If I follow you correctly, I need to have multiple tables. If I make a Products table with just 1 row as Apples and 1 row as Oranges, I have to somehow relate this to another table (Repair Period) with just the distinct Repair Periods. Then the Repair Period table would need to relate to the applicable Repair Actions which would be in a third table. If this is correct, I will start on creating the tables.

    Thanks,
    Andrew

  13. #13
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by drewship View Post
    If I follow you correctly, I need to have multiple tables. If I make a Products table with just 1 row as Apples and 1 row as Oranges, I have to somehow relate this to another table (Repair Period) with just the distinct Repair Periods. Then the Repair Period table would need to relate to the applicable Repair Actions which would be in a third table. If this is correct, I will start on creating the tables.

    Thanks,
    Andrew
    Andrew,

    After taking another look at your Product table, I think what is throwing me of is the two fields:

    ProductName
    ServiceType

    I think you have these field names switched.

    Apples and Oranges sound more like Service Types

    Replace Belt Clip and Replace Buttons sound more like Product (or Service) names.

    Is this correct?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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