Save and Apply Filter Problem with Comboboxes keyed on ID field 1

[fiona108]

Hi There

I have a form with several text boxes and comboboxes. I want my users to be able to filter on any number of controls then save the filter, so it can be accessed and applied at a later date.

I have done this by a button, labelled Save Filter which runs the following code :

Private Sub cmdSaveFilter_Click()

On Error Resume Next

Dim db As Database
Dim qdef As QueryDef

Set db = CurrentDb
Set qdef = db.CreateQueryDef("testFilter", "select * from tblCandidate where " & Me.Filter)

End Sub

This works fine for the text boxes, but not the comboboxes. If I attempt to filter a combobox, Me.Filter looks something like :

((Lookup_tbMaritalStatus.txtMaritalStatus = "Married"))

and when I apply this by using :

docmd.ApplyFilter "testFilter"

I get a Parameter Value box for tbMaritalStatus.txtMaritalStatus.

I would have expected Me.Filter to have read something like :

lngMaritalStatus = 1

I think this is because of a conflict between the ID key field which is the source of the combobox and the text field which is actually displayed.

I am sure there must be an easier way to do this. I cannot find anyting to help me save filters then re-apply them at a later date.

All help gratefully received - thanks.

 

[fiona108]

Solved it - but thanks to anyone who was pondering on it. Instead of saving filters as queries, I wrote the filter (Me.Filter) away to a text field in a table. A description field, in the same table, holds the user entered description for the filter.

These filters and descriptions are loaded into a combobox when the form is opened and the user can then select a pre-defined filter and it is executed with :

Me.Filter = cboAvailableFilters
Me.FilterOn = True