Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2012
    Location
    vENTURA, ca
    Posts
    9

    Unanswered: cascading cboBox with datasheet subform clarity

    Greetings all,

    So the common, limit contents of combo boxes based off selection in other combo box dilemma. I know about the baldy web resource (Forms: Limit content of combo/list boxes), the related posts here, access-programmers.co.uk and the like, Ive been trying to implement the solution for weeks now but still not getting this.

    Ag production db and pretty straightforward: selecting the ranch number in the main form limits the field list in the cboVariety on on the sub form datasheet. List of cboVariety is a query and the linking field would be ranchID

    Ive also referenced nearly every forum posting about this and it seems everyone runs into a problem at a different step, too generic of an explination or no two solutions are the same or that the solution was “jimmy rigged”.

    I have some uncertainties and with these cleared up Im confident I can find solution.

    1. Can you accomplish this, cascading combo box to a subform, with a datasheet as the subform? I get mixed answers.
    2. Exactly what event type(s) do I need in cbo1 and cbo2? I am having trouble here. There are so many combinations on forum postings Ive read on this matter.
    3. Where in the solution to I need to link back to the cboBox and how exactly to I refer to the form (object name or friendly name) and cboBox (properties name or field on form name)? What generally accepted solution to this?

    Is there an order of operation method to this anyone can offer?

    I think to go from here would be best instead of posting my code or examples.

    I appreciate any help and wisdoms, thi sproject has been lingering over my head for some time.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Assumptions
    • You have a table tblRanches, with fields RanchID (numeric) and RanchName (among others)
    • You have a table tblVarieties with fields RanchID (numeric), VarietyID and VarietyName
    • Your form has combo box cboRanch
    • Your form has combo box cboVariety (this is not too much of an assumption)


    Suggestions
    Use the following settings for cboRanch:
    ColumnCount - 2
    ColumnWidths - 0cm; 6cm (or the required width of the control)
    RowSourceType - Table/Query
    RowSource - SELECT RanchID, RanchName FROM tblRanches ORDER BY RanchName;
    BoundColumn - 1
    LimitToList - Yes (Unless you want to write code to add new ranches on the fly).
    Use the following settings for cboVariety:
    ColumnCount - 2
    ColumnWidths - 0cm; 6cm (or the required width of the control)
    RowSourceType - Table/Query
    RowSource - SELECT VarietyID, VarietyName FROM tblVarieties WHERE RanchID = [cboRanch] ORDER BY VarietyName
    BoundColumn - 1
    LimitToList - Yes

    Have a play with that, and see if it gets your on the right path.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Dec 2012
    Location
    vENTURA, ca
    Posts
    9
    weejas, thank you for the reply

    Also for the rundown. The only "event procedure" I implimented was AfterUpdate and mainly just because everything else I read says to do it -

    Private Sub cboCompany_AfterUpdate()
    Me.cboRanch = Null
    Me.cboRanch.Requery
    Me.cboRanch = Me.cboRanch.ItemData(0)
    End Sub

    Do I need this? Anything else?

    So, it works BUT the second combo box, cboRanch, I cant select past the 1st ranch number (there are 5); I click another ranch it wont go, just stays on 1st. I cant fugure why.

    Also, now I have to implement the same solution as above BUT on the subform - subform combo box, cboVariety refering to cboRanch (because there are distinct varieties to each ranch). What changes? Just the criteria/where path to subform?

    ex.
    for 1st solution I used
    [Forms]![frm_pickingMain]![cboCompany]

    for 2nd solution should I use
    [Forms]![frm_pickingMain]![frm_factProduction][cboRanch]
    ?

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Your code is telling the database that after selecting a value in cboCompany, it should remove the value from cboRanch, requery cboRanch and then set cboRanch to the first available item in the list of values. Somehow, I don't think that's what you want to achieve. In fact, I suspect that's what's causing your second issue with it.

    Before I go delving into possibly strategies for the subform, please let me know what you are trying to display on it. If possible, make a copy of the database, remove all sensitive or privileged information from it and upload a zipped copy to the forum, and I'll have a look for you.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Dec 2012
    Location
    vENTURA, ca
    Posts
    9
    I see. Well I took out the event procredure and the same thing happens; cant select beyong the 1st item in list.


    For the cascading combo box in the subform; based upon the ranch selection on the main form, cboRanch, I want to be able to select that ranches varieties in cboVariety on the subform, the linking field being ranchID. From there a series of fields in the subform, plantMonth, appType, block, cycle etc.

    Ive attachd the db. The main form is frm_pickingName and the subform is factProduction.
    Attached Files Attached Files
    Last edited by vega; 12-28-12 at 18:10.

  6. #6
    Join Date
    Dec 2012
    Location
    vENTURA, ca
    Posts
    9
    <ping>, any thoughts?

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Well, it takes a while to unravel someone else's design. Also, it's the holiday season!

    My initial impression is that this is an OLAP cube, which begs the question, why do you need a data entry form for it? I should be able to upload an amended version in a day or so.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Dec 2012
    Location
    vENTURA, ca
    Posts
    9
    Quote Originally Posted by weejas View Post
    Well, it takes a while to unravel someone else's design. Also, it's the holiday season!
    Of’ course, I was just looking at it again this weekend and got nowhere. And cheers, happy holidays!


    Quote Originally Posted by weejas View Post
    My initial impression is that this is an OLAP cube, which begs the question, why do you need a data entry form for it?
    Interesting - what elements exactly is it that make you say this? Just the dim and fact tables? I named them that so that I could discern the function of the objects later (+ so many dimensions to record a transaction). I’m asking from a "beginners mind" aspect. I’m new to much of this but learn fast. As I create ad-hoc Access tools like this one at work we're just implementing SQL Server platform technologies at work and I’m just leaning the difference between OLTP and OLAP.

    Point is, while racking my mind to capture the very dynamic berry production dimensions; I had to design it like this, it is what made sense to me rather. If I can get it to work I will capture everything I need.

    What do you mean a data entry form? I need the end user to enter data daily to populate the fact/transaction table. Considering db methodologies, am I doing something wrong.

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Yes, the naming of the tables implied to me that this is an OLAP database. If that were the case, I would say that you shouldn't need a data entry form, as the data ought to be extracted from an OLTP database (after suitable denormalisation for reporting). [At least, that's how my colleagues work it and the course we attended said that it generally worked. There's no reason that your model is inherently wrong!]

    Having had another look, I've found a problem. Your form seeks to link ranch to company, but the dataset from which the form derives its records does not contain anything directly related to the company concerned. I think that you might need to have a rethink of your basic data model first. Don't delete what you have at the moment, but I would certainly start again with a new set of tables (without records), and build the forms before you start importing the data again.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Dec 2012
    Location
    vENTURA, ca
    Posts
    9
    Thank you for the continued help on this thread.

    On Monday after reading that somebody with a cboBox problem similar to mine solved it by making the the "origin" (for lack of a better term) of their combo box choices the foreign key to the table. Plus this just made more intuitive sense. An image of the revised relationships is attached.

    Unfortunately, this doesn’t work. I still cane make a selection past th first choice in the cboBox.

    I need to solve this before I attempt the second cascading cboBox in the sub form.

    Your form seeks to link ranch to company, but the dataset from which the form derives its records does not contain anything directly related to the company concerned.
    I know, "company" is intentionally an unbound box, I need it to narrow dows the choices of ranchNumber, it works, however, is this not generally accepted practices? That is to have an unbound box present for functionality not needing it in a table?
    Attached Thumbnails Attached Thumbnails localProduction.JPG  

  11. #11
    Join Date
    Dec 2012
    Location
    vENTURA, ca
    Posts
    9
    Im very hands on and visual, is there anywhere that I can see a sample access db similar to Cascading Combos, but which has a sub form cascading combo box implemented? This way I can just back engineer the solution(s)

  12. #12
    Join Date
    Dec 2012
    Location
    vENTURA, ca
    Posts
    9

    any help?

    <ping>... Bueler...?, Bueler....?

    Any help on this? or JUST
    • 1. why I cant make selection on 1st combo box and
    • 2. general "how to" on implementing a second combo box, on a sub form, that refers to first combo box criteria?


    many thanks

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14

  14. #14
    Join Date
    Dec 2012
    Location
    vENTURA, ca
    Posts
    9

    Great, now what about VBA for cboBox and subform cboBox?

    Sinndho,

    Thank you, this is a valuable resource. Now I see how to link forms but im totally confused of what "events" to include (VBA), I know its required, but putting customization aside of anyone's specific solution, what is the plain vanilla event "need" for cascading combo box and combo in sub form.

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Cascading combs basically means that, when the selected item of the first combo (parent or master) changes, the whole selection list of the second combo (child or slave) changes too accordingly.

    - The AfterUpdate event is raised by a combo box when its selection changes.
    - You can retrieve the value in the bound column of the selected row using the Value property of the combo.
    - If you want to retrieve a value in another column, you use the Column(x) property, where x is the zero-based index of the column, which means that the first (leftmost) column is Column(0).
    - You can change the selection list of a combo using its RowSource property.

    As an example:

    - Frm_Customers is the parent (main) form and has a Combobox named Combo_Customers which has 2 columns. The RowSource property of Combo_Customers is:
    Code:
    SELECT Tbl_Customers.ID, Tbl_Customers.Name FROM Tbl_Customers ORDER BY Tbl_Customers.Name;
    - Frm_Sales is the child form (subform) and has a Combobox named Combo_Sales which has 2 columns.

    - When a customer is selected in Combo_Customers, I want that Combo_Sales lists all the Sales related to the selected customer.

    - In the Module of Frm_Customers, we have:
    Code:
    Private Sub Combo_Customers_AfterUpdate()
    
        Const C_SQL As String = "SELECT Tbl_Sales.ID, Tbl_Sales.Sales_Date FROM Tbl_Sales " & _
                                "WHERE Tbl_Sales.Customer_ID=@C ORDER BY Tbl_Sales.Sales_Date;"
        Dim strSQL As String
        
        strSQL = Replace(C_SQL, "@C", Me.Combo_Customers.Column(0)) ' Could be: Me.Combo_Customers.Value
        With Me.Frm_Sales.Form.combo_Sales
            .RowSource = strSQL
            .Value = .Column(0, 0)
        End With
        
    End Sub
    Have a nice day!

Posting Permissions

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