Multiple Filters and a single Combo Box

[JWalcott]

I have created a form to view all of the records in my table. A combo box at the top allows users to quickly find the desired record.
Now I am creating a menu of filters, which the user will select from before the form opens. The form will then only include the records that have passed the criteria of the selected filter. The table is being filtered, but the combo box at the top still contains all records in the original table. I need to make the combo box display only the records that are included after the table has been filtered. There are going to be numerous filters, based on different fields within the table.
How do I make the combo box display the correct filtered list when a filter is selected and display all records when a filter is not selected???
If this is not possible, any suggestions without duplicating the form?

Any help would be appreciated.
Thanks,
Jason

 

[Corticus]

set the rowsource property of the combobox in the same manner you filter the table. I assume your using a query, so you can right click on the field 'rowsource' of the combox properties,and then select 'build' and use the query builder to rebuild the query you perform on the form's table. If you are comfortable with SQL, and maybe you used a dynamic SQL string in the code to filter the table, either way, you can put the SQL from the form's filtering query into the combo box. In the query design view, delete columns you don't want in the combo box (probably just about everything) and then set the column count and column widths property of the combo to display the results from this query as desired. If you have the combobox linked to a field that you need to maintain as your control source but don't want to display, you can make it a 2 column combobox and set the first column to the field you need it linked on, and then set the first columns width to 0". You enter column widths as a string, like 0";1";3"; for a 3 column combo, for example. If you leave the last one blank, however, it adjusts automatically, so for a 2 column combo, just use 0" as the column width, it will hide the first column, while still bound on this field, yet display the second with the width adjusted automatically.

 

[JWalcott]

I can only enter one table or query within the rowsource of the combobox as far as I know. Currently I have it set to the main table which will be filtered.
SELECT [Plans].[ID], [Plans].[Company Name] FROM [Plans]
If I change this to one of the queries which is created when the user makes selections on how the table should be filtered, then what about when no filter is selected or a different query is selected?
Can I store the table name in a text box (not visible) on the menu form based on the user's selections? And have the variable name in the rowsource of the combobox?
For instance a field [Forms]![PlanSelection]![AdministratorFilterField] contains the name of the table/query that has been selected on the menu page.
If I can't use a variable for the table/query, then I need to set up if conditions within the rowsource. Don't know if either option is possible.

 

[Corticus]

If your form is bound to data, it's recordsource will be some sql expression (or just a table). I would assume you are adjusting this expression by selecting criteria on the form so that the form displays the reocrds you are interested in.

In the same manner you modify the recordsource for the form, modify the rowsource for the combobox.

If you are using form filters, then your screwed. I imagine you'll have generate the SQL for the combo's rowsource dynamically in code (I use DAO 3.6), and if you go this route, you might as well lose the form filters and set the form's recordsource to the code-generated sql at the same time you set the combo's rowsource.

Here's an example of a dynamically generated SQL expression in code for a search form:

Private Sub cmd_search_Click()

    Dim sel_sql As String
        
    sel_sql = "SELECT [myTable].[myTableID], " & _
                     "[myTable].[Date], " & _
                     "[myTable].[Number], " & _
                     "[myTable].[Text], " & _
                  "FROM [myTable] " & _
                  "WHERE [myTable].[myTableID]<>0"

    If Not IsNull(Date) Then
        sel_sql = sel_sql & _
          " AND [myTable].[Date]>=#" & txtDate & "#"
    End If

    If Not IsNull(Number) Then
        sel_sql = sel_sql & _
          " AND [myTable].[field2]<=" & txtNumber
    End If

    If Not IsNull(Text) Then
        sel_sql = sel_sql & _
          " AND [myTable].[Text]=""" & txtText & """
    End If

    sel_sql = sel_sql & ";"
    
    [forms]![yourForm].RecordSource = sel_sql
    [forms]![yourForm].Requery
    myCombo.RowSource = sel_sql
    myCombo.Requery

End Sub

Different data types require different notation for criteria values, so this example shows you how to treat a number, text, and date value as criteria in an sql expression.

To recap:
Dates - get number sighnes, ie. #date#
Text - get quotes, ie. "text"
which have to be entered as """ when concatenating a string together
Number - gets no special treatment

Hope this example helps,

 

[JWalcott]

Thank you Corticus. The example you provided has been very helpful. I am using the SQL expression in code to manipulate the table and provide the desired recordset!!! I am still having problems with having the user's input come into the code and with the ComboBox whose recordset is based upon the user's input.

1. Administrator should be = to the value of a combobox on the menu page. This is how I tried to establish the value of the administrator variable type string.
Administrator = Combo120.Value
When the code runs, the user is asked to enter the parameters: Administrator. This should not happen, Administrator = Combo120.Value. Any ideas why this is not working?

2. The ComboBox rowsource: when the code runs, I get an error that an object is required. Here's a piece of my code, perhaps you can tell where I am going wrong.

sel_sql_co = "SELECT [Plans].[ID], [Plans].[CompanyName] FROM [Plans] WHERE[Plans].[Admin] = Administrator"
If Not IsNull(CompanyName) Then
sel_sql_co = sel_sql_co & "AND [Plans].[ID], [Plans].[CompanyName]=""" & txtTXT & """"
End If


sel_sql_co = sel_sql_co & ";"
Combo110.RowSource = "SELECT [Plans].[ID], [Plans].[Company Name] FROM [Plans]"
Combo110.Requery

Thanks for any help.

 

[Corticus]

1. Administrator should be = to the value of a combobox on the menu page. This is how I tried to establish the value of the administrator variable type string.
Administrator = Combo120.Value
When the code runs, the user is asked to enter the parameters: Administrator. This should not happen, Administrator = Combo120.Value. Any ideas why this is not working?

Are these two controls on the same form? If not, you must refer to the form from which you are getting a value (not the form where the code in initiated) explicitely like [forms]![myForm].[txt1]

2. The ComboBox rowsource: when the code runs, I get an error that an object is required. Here's a piece of my code, perhaps you can tell where I am going wrong.

Sounds like the same problem, check how you refer to one form from another.