Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help on command bar (97)

    Help on command bar
    I

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help on command bar (97)

    I'm not quite sure what you're trying to do here, nor why you're deleting and recreating a toolbar, but the expression you're trying to evaluate in your Select Case returns a pointer to the Controls collection for that menubar. A select case should return a result that you can compare to each of its cases. You can't compare a collection to whether some member of it is null, which is what you're trying to do. In this instance, a Select Case isn't the appropriate structure for your code. Try this instead (and note that the references to the combobox controls inside the With-End With have a dot in front of them:

    <pre>Dim cbr As CommandBar

    Set cbr = CommandBars("Filter Menu")

    With cbr
    If Not IsNull(.Controls("RegionCombo")) And Not IsNull(.Controls("ManagerCombo")) Then
    MsgBox "Provide only one value, REGION or MANAGER, not both.", vbOKOnly
    DoCmd.RunMacro "mcrDeleteToolbar"
    DoCmd.RunMacro "mcrCreateToolbar"

    ElseIf IsNull(.Controls("RegionCombo")) Then
    strFilter = "qryDtlMgrMth6.DirectorCode =" _
    & Choose(.Controls("regionCombo").Index, 1500, 1300, 1400, 1100, 1200) _
    & " AND qryDtlMgrMth6.Year =" & filterYear _
    & " AND qryDtlMgrMth6.promotionType='" & filterType & "'"

    ElseIf IsNull(.ManagerCombo) Then
    strFilter = "qryDtlMgrMth6.Code =" _
    & Choose(.Controls("managerCombo").Index, 1500, 1300, 1400, 1100, 1200) _
    & " AND qryDtlMgrMth6.Year =" & filterYear _
    & " AND qryDtlMgrMth6.promotionType='" & filterType & "'"
    End If
    End With

    Set cbr = Nothing</pre>


    I'm assuming here that regioncombo and managercombo are the names of the two comboboxes on your "filter menu" commandbar. If that isn't valid, you'll have to play with the code. Even if the code compiles, I think you'll have problems, though, because you're first testing to see if a combo is null and then, if it is, you're trying to use its index to choose a value. Wouldn't you want to do it the other way around? By that I mean, if the RegionCombo is null, use the ManagerCombo information and vice versa?
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help on command bar (97)

    Hi Charlotte, thanks for the response! I

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help on command bar (97)

    Charlotte, the first IF condition you supplied is giving me an INVALID PROCEDURE CALL OR ARGUMENT even though I have a value in one of the combo boxes.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help on command bar (97)

    It was air code and only intended to get you started in another direction. Try taking out the with-end with and using the full reference for the controls. Depending on how your commandbar is set up, you may have to modify the references. Here's a sample of a routine that takes action based on a combobox value, but it uses the ListIndex property, not the Index property of a commandbar combobox. BTW, the code is fornatted using Pre tags from the tag panel. For that reason, if you copy formatted code like this, paste it into Word first. Then copy it from there and paste it into your module.
    <pre>Public Function cbEvalCombo(ByVal strMenuName As String, _
    ByVal strCBOName As String)
    Dim cbo As CommandBarComboBox
    Dim cbr As CommandBar

    Set cbr = CommandBars(strMenuName)
    Set cbo = cbr.Controls(strCBOName)
    Select Case cbo.ListIndex
    Case 1
    MsgBox "First item selected"
    Case 2
    MsgBox "Second item selected"
    End Select
    Set cbo = Nothing
    Set cbr = Nothing
    End Function</pre>


    It seems to me the easiest way to handle your problem would be to make the first item in your list an empty string. Then they could always select that to clear the value or you could set ListIndex to 1 to clear it and you wouldn't have to delete and recreate the commandbar.
    Charlotte

Posting Permissions

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