none
data in RowSource for 3 controls depends on which is selected first. If cboDataSource is picked first then one set of values for cboType and the Listbox. If cboType is picked first slightly different values for other cbo and listbox. RRS feed

  • Question

  • Just to make it more interesting. The second cbo picked ALSO modifies the RowSource of the listbox. 

    Set db = CurrentDb 
    Set qdf = db.QueryDefs("MyQuery")
    
    For Each prm In qdf.Parameters 
            prm.Value = Eval(prm.Name) 
    Next prm 
    
    Set rs = qdf.OpenRecordset(dbOpenDynaset) 
    

    1. I thought of getting the values using the above code.

    To get RowSource values for the  controls, but I don't know how to get control's RowSource from the above rs. For both cbo's and the Listbox.

    2. I thought about using one table setup like this.

    one field holding only the values possible in cboDataSource and have one record for each unique combo of cboDataSource and cboType afterUpdate values. For each unique combo of cboDataSource and cboType there maybe zero to 15 values used for the ListBox.

    So ends up with quite a few records for very few items in both cbo's.

    It seems I am missing a better way to come at this. I wish the order of selecting the two cbo's did not make a difference, but it does.

    I will be very grateful for any ideas on a better way to do this. I will be grateful even if I find out I am stuck making the big table and have to walk thru the table in DAO record set, comparing values in table against values of combo Boxes if one matches, then update a "pickThisRecord" field by +1, then get only records with the "max" value in "pickThisRecord" because those records would be the set of records holding the values I needed.

    Any hints on any of this would be great. Even finding out there is no better way, would be helpful.

    Thanks,

    Mark J


    Mark J

    Tuesday, July 3, 2018 3:52 PM

Answers

  • Hello Mark J,

    >>I thought of getting the values using the above code

    You could try something like

    Me.Combo2.RowSourceType = "Table/Query"
    Set Me.Combo2.Recordset = rs
    Me.List11.RowSourceType = "Table/Query"
    Set Me.List11.Recordset = rs

    >>I thought about using one table setup like this.

    We support one issue for one thread. I would suggest you post a new thread for this design. I would suggest you share more detail for your design. For instance, I'm still wondering what's relationship with these combo boxes and list box. I think it will be more clear if we could know why you need change other controls row source.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by PuzzledByWord Friday, July 6, 2018 10:43 AM
    Wednesday, July 4, 2018 9:15 AM

All replies

  • Hello Mark J,

    >>I thought of getting the values using the above code

    You could try something like

    Me.Combo2.RowSourceType = "Table/Query"
    Set Me.Combo2.Recordset = rs
    Me.List11.RowSourceType = "Table/Query"
    Set Me.List11.Recordset = rs

    >>I thought about using one table setup like this.

    We support one issue for one thread. I would suggest you post a new thread for this design. I would suggest you share more detail for your design. For instance, I'm still wondering what's relationship with these combo boxes and list box. I think it will be more clear if we could know why you need change other controls row source.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by PuzzledByWord Friday, July 6, 2018 10:43 AM
    Wednesday, July 4, 2018 9:15 AM
  • Terry Xu,

    Thanks for the info that I can use a DAO Dynaset RecordSet as the RowSource of a Combo Box and a List Box that makes this much easier.

    I will mark this as answer. 

    Next to trying this approach. 

    Because looks like I will now need to have the returned record set take into account the order of the selections of 4 combo boxes.

    When the RowSource of each control will be different based on which control has a value selected first, second, third and fourth.

    When any combo Box has after Update event. Call a Private Sub CreateWhereClause procedure to assemble a WHERE clause for the recordSet. that is used for each control's RowSource.  Using IIF to handle if a combobox value IsNull. by ignoring that combobox in making the where clause.

    Seems what is next is to see how to set up RecordSet to have all data in one record set.

    Thanks again for the info.

    Mark J


    Mark J

    Friday, July 6, 2018 10:43 AM