Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form filtering (2007)

    This is pretty much a continuation of <post:=656,131>post 656,131</post:> where I added a "team member" datasheet subform to my database. Now that i have started changing it... i can't stop! i want to add a section to my form that lets the user filter projects based off of the employee ID (or team member). If i were to do this, do i need to create a query and base the entire form and all of its fields off of it? I don't remember the first thing about creating a filter at this point...
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Form filtering (2007)

    You could place an unbound combo box on your main form similar to the bound combo box on your subform.
    In the After Update event of the combo box, you can filter the main form:
    <code>
    Private Sub cboSelectEmployee_AfterUpdate()
    If IsNull(Me.cboSelectEmployee) Then
    ' Turn filter off
    Me.Filter = ""
    Me.FilterOn = False
    Else
    ' Turn filter on
    Me.Filter = "ID In (SELECT ProjectID FROM ProjectEngineers WHERE EmployeeID = " & Me.cboSelectEmployee & ")"
    Me.FilterOn = True
    End If
    End Sub
    </code>
    Substitute the correct names where necessary.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form filtering (2007)

    i tried that and made the row source = to the row source of the datasheet combo box. but they have different control source so i get an error when i run it. do i need to make a whole subform for it?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Form filtering (2007)

    The new combo box should be unbound, i.e. its control source should be empty. This combo box is not intended to store data, just to let you select an employee to filter on.
    There's no need to create a subform for it.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form filtering (2007)

    but even though it is unbound with no control source, wouldn't i want the row source to be correct so that i could choose someone to filter on?

    SELECT ID, <!t>[Employee Name]<!/t> FROM <!t>[Employees Extended]<!/t> ORDER BY <!t>[Employee Name]<!/t>;
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Form filtering (2007)

    Yes, that is what I suggested in my first reply.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form filtering (2007)

    I just don't get any names in my combobox. Depending on what i set my "row source type" to i get different items in the comobobox. if i select Table/Query i get a list of numbers that aren't in order (i'm guessing this is the employees ID's). if i select value list or field list i get the actual field names of the table which cause it to try and add a new entry every time.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Form filtering (2007)

    Set the following properties for the combo box:
    Column Count: 2
    Column Widths: 0";1"
    Row Source Type: Table/Query
    Row Source: the SQL statement you already had
    Bound Column: 1

  9. #9
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form filtering (2007)

    Column Count: 2
    Column Widths: 0";1"


    i should have known to change this since i could only see the numbers. ah well, hopefully next time i will. Thanks for the help Hans, I'm all set now.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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