How Do I Make a 'LIVE' Filtering Form?

S.R.WALKER · #1 (**permalink**) 02-01-12, 03:44

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.

Sinndho · #2 (**permalink**) 02-03-12, 05:23

Quote:

Originally Posted by S.R.WALKER

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

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).

S.R.WALKER · #3 (**permalink**) 02-04-12, 04:13

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.

Sinndho · #4 (**permalink**) 02-04-12, 05:20

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

S.R.WALKER · #5 (**permalink**) 02-04-12, 21:32

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?