Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Unanswered: How Do I Make a 'LIVE' Filtering Form?

    Hey There,
    Firstly, Please excuse my noobiness and please don't hesitate asking me any questions or correcting me.

    I'm currently trying to create an extension listing.
    It should display the appropriate name and extension.
    A user should be able to select in a listbox. (all or just one field)

    - Location
    - Branch
    - Department

    When selecting these from the listbox I'd like to have a table embedded so the user can view as you filter and can easily change.

    I require assistance creating this part of the form.
    I have already created all the data (all the appropriate tables)

    I'm sorry if this isn't clear enough, please ask me any questions if you need.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by S.R.WALKER View Post
    When selecting these from the listbox I'd like to have a table embedded so the user can view as you filter and can easily change.
    What do you mean with that?
    Quote Originally Posted by S.R.WALKER View Post
    I have already created all the data (all the appropriate tables)
    Please provide information on the tables involved in the process (Table name, Columns names and data types).
    Have a nice day!

  3. #3
    Join Date
    Feb 2012
    Posts
    3
    I don't want to run a query from a form.
    I want the data to be displayed on the same form as you filter from the drop down lists (Location, Department, Branch)

    This is so that a user can quickly and easily change the filters without having to reopen to form or go 'back' to the page.

    This will eventually be a front end on the company intranet.

    I currently having the following tables, with columns in brackets)

    Employees (Employee ID - First Name - Last name - Phone Number - Extension - Location ID - Department ID - Branch ID)

    Location (Location ID - Location)

    Branch (Branch ID - Branch)

    Department (Department ID - Department)



    Within the 'Employees' Table Branch ID, Department ID and Location ID all link the the relevant tables and are a listbox.

    Right now, it is easily possible to make a form and to run a query to display the appropriate data.
    However, this will open the query in a new window with the results and this is not what is the desired result.

    If you require anymore information please let me know,

    Appreciate your time.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Create 3 queries:

    a) Qry_Combo_Branch
    Name: Qry_Combo_Branch
    SQL:
    Code:
    SELECT Branch.[Branch ID], Branch.Branch
    FROM Branch
    ORDER BY Branch.Branch
    UNION SELECT 0, ' <All>'
    FROM Branch;
    b) Qry_Combo_Department
    Name: Qry_Combo_Department
    SQL:
    Code:
    SELECT Department.[Department ID], Department.Department
    FROM Department
    ORDER BY Department.Department
    UNION SELECT 0, ' <All>'
    FROM Department;
    c) Qry_Combo_Locationt
    Name: Qry_Combo_Location
    SQL:
    Code:
    SELECT Location.[Location ID], Location.Location
    FROM Location
    ORDER BY Location.Location
    UNION SELECT 0, ' <All>'
    FROM Location;
    2. Add 3 combo boxes to the form (also works with list boxes):
    a) Combo_Branch
    Name: Combo_Branch
    Row Source Type: Table/Query
    Row Source: Qry_Combo_Branch
    Bound Column: 1
    Column Count: 2
    Column Width: 0cm;3cm (convert into inches if necessary)
    After Update: =SetFilter()

    b) Combo_Department
    Name: Combo_Department
    Row Source Type: Table/Query
    Row Source: Qry_Combo_Department
    Bound Column: 1
    Column Count: 2
    Column Width: 0cm;3cm (convert into inches if necessary)
    After Update: =SetFilter()

    c) Combo_Location
    Name: Combo_Location
    Row Source Type: Table/Query
    Row Source: Qry_Combo_Location
    Bound Column: 1
    Column Count: 2
    Column Width: 0cm;3cm (convert into inches if necessary)
    After Update: =SetFilter()

    3. Add this function in the Form Class module:
    Code:
    Private Function SetFilter()
    
        Dim strFilter As String
        
        If Me.Combo_Branch.value <> 0 Then
            strFilter = "[Branch ID] = " & Me.Combo_Branch.value
        End If
        If Me.Combo_Department <> 0 Then
            If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
            strFilter = strFilter & "[Department ID] = " & Me.Combo_Department.value
        End If
        If Me.Combo_Location <> 0 Then
            If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
            strFilter = strFilter & "[Location ID] = " & Me.Combo_Location.value
        End If
        If Len(strFilter) > 0 Then
            Me.Filter = strFilter
            Me.FilterOn = True
        Else
            Me.FilterOn = False
        End If
        
    End Function
    Have a nice day!

  5. #5
    Join Date
    Feb 2012
    Posts
    3
    I have now done as you've said.

    It comes up with the error;


    "The expression After Update you entered as the event property setting produced the following error: The expression you entered has a function name that Microsoft Office Acceess can't find.




    Also, I'm unsure how to create a table within the form to display the data?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Check that the line for the AfterUpdate property of the combos is exactly:
    Code:
    =SetFilter()
    2. Check that the function resides in the class module of the form where the combos are located.

    3.
    I'm unsure how to create a table within the form to display the data?
    I don't understand what you mean.
    Have a nice day!

Posting Permissions

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