Help on command bar (97)

Topic

New Reply

Help on command bar
I

Reply | Quote

Replies

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:

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

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?

Reply | Quote

Hi Charlotte, thanks for the response! I

Reply | Quote

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.

Reply | Quote

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.

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

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.

Reply | Quote