Interactive filtering/querying

[Tim L]

I have an idea to be able to control the data displayed on a form automatically depending on what is displayed/selected in a combo box.

I have already implemented something on one database but this requires that *something* is selected in the combo boxes used. I would like to be able to leave the combo box blank, so that all records but this doesn't work. I currently use a named query, or the raw SQL in the Record Source, which looks at the value of the combo box directly.

This produces two problems:

Only one item can be selected from the list (I know that it is 'possible' to use a multi-select listbox, but I haven't figured out how to use one of those yet either - not and actually get any results in a query).

If nothing is selected in the combo box then nothing is returned (I've tried putting * in the combo box with the same useless result).

I have gone to pains to not use the word 'filter' anywhere above because as far as I am aware I haven't actually used a Filter, which seems to be something different to a query. Would using a filter be a better way to achieve what I want and if so, how do I go about implementing one? (Or to put it another way; what is the difference between a filter and a query and how are filters implemented?)

My current train of thought suggests that I could perhaps test the value of the combo box and if the value is "ALL" to manually set the Record Source to the basic SQL statement that returns all records. I am sure that I can do this and that it would work but I figure that there may be a more elegant method (and perhaps more efficient?) method.

Advice and suggestions welcomed.

Tim

 

[dgm]

So u have something like this?

SELECT * FROM myTable WHERE [myID] = " & forms!myForm!myCombo

I've done something similar except instead of referencing the combo directly, i call a function that returns the value of the combo, and if it's blank u can return something else like '*'.

so u would have:

SELECT * FROM myTable WHERE [myID] like '" & getCombo() & "'"

I think that would work anyway... As i said i'm not really doing the same thing, but kind of similar.

And your function would look something like this:

Function getCombo() As String
    getCombo = forms!myForm!myCombo
    If isnull(getCombo) then
        getcombo = "*"
    End If
End Function

If u wanted to use a filter, your code for the combo afterupdate would be something like this:

If isNull(myCombo) then
    strFilter = "[myId] like '*'"
Else
    strFilter = "[myId] = " & myCombo
End if
me.filter = strFilter
me.filterOn = true

So all the records are still being passed to your form, where they are then filtered according to your conditions.

If u have a lot of records it would probably be quicker to use the query as less data is being transferred. Otherwise it's probably not going to make much difference.

But then again... i could be wrong.