Fun Filter Problem 2

[joopa]

So, I have form with 3 fields: Primary_Chemical_Name, Secondary_Chemical_Name and Catalog_No. All Alphanumeric.

I want to make One text box for filtering the records. When the user writes the first charaters or numbers in the text box I want to automatically filter based on the entry in all fields. It is a given that none of the Fields are identical.

I realize I have to use On_exit or Lost_focus event and use the DoCmd.ApplyFilter on the text box. But I can't seem to get it to work.

I would like not to have a saved filter, but write it directly in the code. Will that work?

Thanks,
JJ

 

[peterpruimboom]

Hai Joopa,

On a bound form a relative simple way of doing this is:

In your forms recordsource you put a query that looks like

SELECT Field1, Field2, Field3
FROM TableName
WHERE Field1 Like [Forms]![FormName]![txtFilter] & "*"

Every time your txtFilter changes you do a requery of your form and you will only see the records that starts with the characters you type in the textbox.

When you need to test more fields just use OR in the WHERE section as follows:

SELECT Field1, Field2, Field3
FROM TableName
WHERE Field1 Like [Forms]![FormName]![txtFilter] & "*" OR Field2 Like [Forms]![FormName]![txtFilter] & "*"

That way you filter more fields in one go.

Hope this helps.

 

[joopa]

Thanks, Peterpruimboom
It works great! when I have my form alone. But this is a subform, embedded into a main form.

I get an inputbox asking me for the txtFilter value each time I open the main form? And the same happens when I do a Shift+F9 requery.

Any ideas?

Thanks

 

[willir]

joopa

The txtFilter prompt probably means that you have a typo or have not defined something correctly. When the form loads, and Access finds an undefined variable referenced in the form, it will treat it as a parameter variable and prompt you for a vlaue.

Moving on...
When working from your main form, you have the ability to change the record or data source, not just the filter string.

    Me.reading_sbfrm.Form.RecordSource = strSQL1
    Me.reading_sbfrm.Requery

Where the SQL select string can reference a different conditional where clause or even a different table. Just make sure the variables in the select statement source and the form variables match.

My code come comes a utility meter reading system that toggles from the the meter history file to a temp file where I calculate consumption. In your case regarding chemical names, for example, you could switch from the universal numbering system to the universal language system for the chemical name.

 

[joopa]

Great,
I moved the text box from the sub form to the main form and it works Like a dream. I don't even have to do the requery, it does it automatically.

Thanks a lot.

 

[peterpruimboom]

Joopa,

Filling the recordsource does an automatic requery. that's why the requery isn't needed.