Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    6

    Question Unanswered: combo-box/cascading question

    SORRY IF I POSTED THIS TWICE....

    Okay, I've enclosed a sample of the kind of data and I'm dealing with in this database. There's more to it, but for our purposes I just wanted to show the main fields.

    The fields are:

    WACCOUNT (Note W stands for "West")
    WSUBACCOUNT
    WSUBLEDGER
    WSUBSIDIARY
    EACCOUNT (Note E stands for "East")
    ESUBACCOUNT

    I need the user to be able to input the WACCOUNT, the WSUBACCOUNT, and WSUBLEDGER (where this exists) and receive output on:

    W SUBSIDIARY
    EACCOUNT
    ESUBACCOUNT

    There are no direct relationships between WACCOUNTs and EACCOUNTS, WSUBACCOUNTs & ESUBACCOUNTs, or even the combination of WACCOUNT/WSUBACCOUNT & EACCOUNT/ESUBACCOUNT.
    It is vital that the user input all three variables: WACCOUNT, WSUBACCOUNT, and WSUBLEDGER.

    It's been suggested that I might use cascading combo-boxes--and I'll look into that. In the meantime, if someone could please offer some recommendations I would greatly appreciate this.



    In an attempt to prevent some confusion....

    The first field selected (WACCOUNT) should determine what the user's options are for the second (WSUBACCOUNT), and likewise the second determines the available entries for the third field (WSUBLEDGER). After all of this is entered, the user hits Enter and all of the East data is populated.

    Hope that helps.
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You're gonna need to bite the bullet and write some VB code attached to the combo box's OnChange event. The code should do this:

    OnChangeA
    Set box B datasource. (You may also need to requery it.)
    Clear box B's value.
    Set boxB Enabled = True
    Clear box C's value.
    Set boxC Enabled = False

    OnChangeB
    Set box C datasource. (You may also need to requery it.)
    Clear box C's value.
    Set BoxC Enabled = True

    B and C should be disabled by default.

    The only way I can think to do it without VB would be to have three nested layers of linked subforms showing all distinct possible options for each individual element. Your query would filter on whatever records the user had selected in each level of the form. That way Access would do the filtering and refreshing for you.

    Still, the VB code will be less trouble in the long run.

    blindman

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Here is a similar answer I did using two dates as criteria but the concept is the same (query by form).

    Have a form perhaps called MyForm with the following 2 controls:
    ctrDateMin
    ctrDateMax

    And have a command button on the form to open the report with something like the following code behind the OnClick property:
    DoCmd OpenReport "MyReport", A_PREVIEW
    DoCmd Maximize

    Of course the form could also have a command button to open another form with something like the following code behind the OnClick property:
    DoCmd OpenForm "MyOtherForm"
    DoCmd Maximize

    Base the report on a query. In the query's criteria for the date field perhaps put in something like the following:
    Between [Forms]![MyForm]![ctrDateMin] And [Forms]![MyForm]![ctrDateMax]

    And of course if the command button were opening a form instead of a report, the form could also be based on the same query or a similar one.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  4. #4
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    And here is a way to have a second combo box (perhaps listing products) based on another combo box (perhaps listing companies). Thus after the user chooses a company, he then sees only the products for that company in the second combo box.

    For criteria in the 2nd combo box (and to get to the query design mode of the combo box click on the 3 dots just to the right of the combo box's RowSource property):
    Like [Forms]![MyForm]![MyFirstComboBox]

    And for the OnEnter property of the 2nd combo box:
    =ctlRequery()

    And in any module:
    Public Function ctlRequery()
    ' Purpose: Use this in OnEnter property of a combobox that's based on another control
    ' that may have changed or based on records that may have changed
    ' since the form was first opened.

    On Error GoTo Err_ctlRequery

    ' Dim var.
    Dim ctl As Control

    ' Set var.
    Set ctl = Screen.ActiveControl

    ' Requery control.
    ctl.Requery

    Exit Function

    Err_ctlRequery:
    Exit Function

    End Function

    For an example of dependent combo boxes you can download my sample invoices database currently at the link toward the bottom of http://www.bullschmidt.com/login.asp and then on the Invoices Dialog check out the Invoice # combo box which is based on the Rep # combo box.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

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