Filters on forms based on combo boxes 1

[JDRoss]

I am wondering what is the best way to go about filtering on a form.

I have about three combo boxes that users might select from to filter the form in Datasheet view. However, I am never quite sure what the best way to go around this is.

Should you?

A) use the form's me.filter
b) use a value on the underlying query for the forms data set to the combo box in the form e.g. form!Myforms!combobox and then requery
C) run sql code

I know this is a broad selection. I find that when I use the latter method, which I would prefer, the form doesn't requery very well.

I'd appreciate any advice or help

Regards

John

 

[Savil]

If you wish to use SQL, try this method.
The first thing to do would be to create a function that creates the filter for you
When you click on one of the the combo boxes the event can call the function but pass its value to the function eg

click on Combo2 and the event calls the function BuildFilter(me!Combobox)notice you are passing the value as an argument to the function.

Public Function BuildFilter(ValIn As String)

forms!FrmName.recordsource = "SELECT * FROM Blah WHERE FldName = '" & ValIn & "'"
forms!FrmName.requery

End Function

To further enhance this you call this function from any combo box by passing the field name the particular combo box performs its filter on, BuildFilter(me!Combobox,FldName)

Public Function BuildFilter(ValIn As String,fldIn As String)

forms!FrmName.recordsource = "SELECT * FROM Blah WHERE " & fldIn & " = '" & ValIn & "'"
forms!FrmName.requery

End Function

Hopefully you can see the method in my madness

Good Luck

 

[JDRoss]

Thanks Savil

That makes it look easy. I think I see where you are going. Setting the recordsource for the form is what I had been missing.

I might have a further complication too, sometimes I need to have one combo selection take into account what is in another already. I think that can be managed too?

Anyway, thanks once again

Appreciated. I'll let you know how I get on.

Regards

John