Results 1 to 12 of 12
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Cascade Combo (2002 SP-2)

    In an unbound form with unbound Combo Box(s) I am selecting a customer from combo1 and a customer service rep. from combo2. Combo2 only displays customer service reps based on the customer selected in combo1 - no problem. However, if I make a selection from combo1 and then combo2, but then decide I have the wrong customer and change combo1, combo2 still displays the customer service reps from the choice first selected in combo1. I suppose I need to requery the form, but not sure where it should be done (what event).

    Also, I would like to have a list of products (with the option of selecting "all") on this form also. Any input on how best to do this appreciated. Product list (tblCommodities) only has a dozen or so entries and I can't imagine it going beyond fifty or so.

    This form is being used for query parameters.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cascade Combo (2002 SP-2)

    What code do you have to make Combo2 display only customer service reps for the customer selected in Combo1? It's strange that it would only work the first time you select a customer.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Cascade Combo (2002 SP-2)

    Hans:

    Code?! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cascade Combo (2002 SP-2)

    Open the form in design view.
    Select Combo1.
    Activate the Event tab of the Properties window.
    Click in the After Update event, and select [Event Procedure] from the dropdown list.
    Click the ... to the right of the dropdown arrow.
    Make the code look like this, with the appropriate name substituted for Combo2:

    Private Sub ComboCustomer_AfterUpdate()
    Me.Combo2.Requery
    End Sub

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Cascade Combo (2002 SP-2)

    Hans:
    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>...Oh! You mean you actually have to name the control? You can't just put "Me.Requery"? <img src=/S/bash.gif border=0 alt=bash width=35 height=39>
    Thanks! Any idea's on a "pick-list? (All, one, some, none)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cascade Combo (2002 SP-2)

    Me.Requery would force the form as a whole to re-read its record source, but you only want to force "Combo2" to re-read its row source.

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Cascade Combo (2002 SP-2)

    Hans:

    . . .that's why I was laughing at myself. Re-read the record source of an unbound form? What was I thinking?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cascade Combo (2002 SP-2)

    About the product list:

    - Create a query based on tblCommodities that selects the field(s) you want to display, with the primary key as first field, and set the sort order the way you want.

    - Put a list box on the form with the query as Row Source. Set the number of columns and the column widths to display the fields you want to display.

    - Set the Multi Select property of the list box to either Single or Extended. Single means that you can select and deselect list items by clicking them. Extended means that you can select items the way you can select files in Windows Explorer: click to select a single item, click+drag or click+Shift click to select a contiguous range, and Ctrl+click to select/deselect individual items.

    - You can put command buttons next to the list box to select all items and to deselect all items. For example (names are fictitious):

    Private Sub cmdSelectAll_Click()
    SelectProducts True
    End Sub

    Private Sub cmdDeselectAll_Click()
    SelectProducts False
    End Sub

    Private Sub SelectProducts(Flag As Boolean)
    Dim i As Long
    For i = 0 To Me.lstProducts.ListCount - 1
    Me.lstProducts.Selected(i) = Flag
    Next i
    End Sub

    - You will need code to do something with the selected products. This depends on what you want to do with them.

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Cascade Combo (2002 SP-2)

    Thanks!
    <img src=/S/munch.gif border=0 alt=munch width=19 height=17> . . .I'll see what I can do to mess this up.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Cascade Combo (2002 SP-2)

    Hans:
    (I'll let you be the judge of whether this needs to be a new post, because this is going into a whole new region of thought)

    I have the list box built, but before we go any further I will describe what the form is used for. The form is used to enter multiple parameters for a query upon which a report will be generated. Most all of the criteria are absolute (single selection: customer, service rep., date, destination, etc.) but the commodity could be one of several, multiple, or all. Thinking in terms of the query, I understand using [Forms]![Form1]![Source1] for the criteria (or even Between...And) but not how it could read from a multi-select source like a list box. I will (eventually) be building the query with a txtCommodity row with criteria built upon this list box of the form. If we're headed in the right direction, I'm all ears!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cascade Combo (2002 SP-2)

    In this case, it is easier to leave the criteria for commodity (product) out of the query, and instead, open the report with a WhereCondition argument constructed in code. Something like (fantasy names again):

    Private Sub cmdOpenReport_Click()
    Dim i As Long
    Dim strWhere as String

    On Error GoTo ErrHandler

    If lstProducts.ItemsSelected.Count = 0 then
    MsgBox "No products selected!", vbExclamation
    Exit Sub
    End If

    For i = 0 To lstProducts.ListCount - 1
    If lstProducts.Selected(i) = True Then
    strWhere = strWhere & ", " & lstProducts.ItemData(i)
    End If
    Next i

    ' Create string of the form IN (12, 23, 35)
    strWhere = "[ProductID] IN (" & Mid(strWhere, 3) & ")"

    ' Open report with where-condition
    DoCmd.OpenReport "rptSomething", acViewPreview, , strWhere
    Exit Sub

    ErrHandler:
    If Err <> 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    This code assumes a numeric ProductID; if it is a text field, use this line:

    strWhere = strWhere & ", " & Chr(34) & lstProducts.ItemData(i) & Chr(34)

  12. #12
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Cascade Combo (2002 SP-2)

    Hans:

    Thanks so much for that! I'll finish building the form and parameter query, then try this out in the report. Probably won't get around to finishing before the weekend, so I'll make a new post then if (when) I need more help. Deeply appreciative!!

    EDIT: Issue of addressing list box(s) in WhereCondition argument (as opposed to query parameters) is addressed in <post#=383873>post 383873</post#>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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