Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Posts
    21

    Unanswered: Filter records using selection from a combo box

    I have a form and the form's control source is "tblProjects". By default the records are shown in ascending order by "ProjectID" from the table. But I'd like to add a combo box to the form which when the user clicks the drop down arrow, "ProjectOwner" from the table appears. I know how to create the combo box. But what I want to happen is for the records to be shown are ONLY the records that the selected ProjectOwner has. I also want to provide an "ALL" choice in the drop-down and then have the records appear again for all records sorted by "ProjectID" as they are when the form is first opened. How can I do this?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    After Updating the combo box, set a filter:

    Me.Filter = "ProjectOwner = '" & Me.ComboBoxName & "'"
    Me.FilterOn = True


    And your All button just needs

    Me.FilterOn = False

    That should do it If your project owners have apostrophe's in their name then it would need some """adjustment"""".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Mar 2008
    Posts
    21
    Perfect so far. But I thought of one more thing... if the Project Owner has no records I want a message box to pop up indicating such. How would I handle that?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Just keep on coding! If there are no results, your user will be at the "new" record, so you could test for that and act accordingly...

    Code:
    Me.Filter = "ProjectOwner = '" & Me.ComboBoxName & "'"
    Me.FilterOn = True
    DoEvents
    If Me.NewRecord Then
      MsgBox "There are no results to display."
      Me.FilterOn=False
    End If
    ... but it may be even wiser to test to see if there are going to be results before even applying the filter. It would be faster and more professional, but the coding gets more complex than this is.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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