Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter by Selection code (2000)

    I have a form based on a qyr. When the form is showing and I click on: Records>Filter>Filter by Selection I get the desired results: only records with the current selection are left.
    However, when I add a cmd btn with the following code, I get an error msg: The command or action 'FilterBySelection' is not available now.

    <font color=blue>Private Sub FilterBySel_Click()
    On Error GoTo Err_FilterBySel_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 0, 1, acMenuVer70


    Exit_FilterBySel_Click:
    Exit Sub

    Err_FilterBySel_Click:
    MsgBox Err.Description
    Resume Exit_FilterBySel_Click

    End Sub</font color=blue>

    I tried this:
    <font color=blue>DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70</font color=blue>

    and then removing the filter with
    <font color=blue>DoCmd.DoMenuItem acFormBar, acRecordsMenu, 3, , acMenuVer70</font color=blue>
    but then when I select a new control and click the FilterBySel btn it reverts back to the previous filter (not the current selection).

    How can I make the original code (top, above) work?
    (DoCmd.DoMenuItem acFormBar, acRecordsMenu, 0, 1, acMenuVer70)

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

    Re: Filter by Selection code (2000)

    The problem is that by clicking the command button, the selection you had made loses focus, and therefore filter by selection is not possible any more. But there is a button on the toolbar for filter by selection, why not use that?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter by Selection code (2000)

    Thanks Hans

    With your help I solved the problem by adding the following line
    Screen.PreviousControl.SetFocus

    before the DoCmd

    I'm wanting to use cmd buttons on the form for certain operations to make it simpler for those not too experienced with the various Access menus
    Thanks

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

    Re: Filter by Selection code (2000)

    OK. Please note that the DoCmd.DoMenuItem code is rather antiquated, it is a remnant from Acces Basic in Access 95. You can use RunCommand acCmdFilterBySelection instead.

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

    Re: Filter by Selection code (2000)

    Another comment: the Filter By Selection button on the toolbar will let you filter on part of a field. The code behind your command button will only let you filter on the entire field contents, because setting back the focus to Screen.PreviousControl will not restore the original selection within that control.

Posting Permissions

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