Filtering a records displayed in a form 1

[TechieJr]

Hi everyone,

I am looking for a way to filter records shown in a form which has been based on a table. This has to be done "behind the scenes" so to speak, so having the user apply a filter is out of the question. Also, because of the comlexity of the form and subforms, calculations, etc. it is too late to try and rework it based on a query.

I have noticed that there is a "Filter" property on the "Data" page of the form's property sheet. I can't seem to find anything to tell me how to use it. Do one of you know? Would this solve my problem?

Thanks for your help.

 

[FancyPrairie]

The first thing you need to do is set you Filter property to what you want. Then set the FilterOn property to True.

For example:

Me.Filter = "strName = 'Tom'"
Me.FilterOn = True

 

[TechieJr]

Thanks for a quick reply FancyPrairie,

Your suggestion looks as if it would be in a macro or vba code somewhere. Am I right? I am using Access 2002 and there is no "FilterOn" property listed on the Data tab of the form's property sheet. Among others, there the following lines: Record Source, Filter, Order By, Allow Filters (Y/n), Allow Edits (Y/n), etc.

If I need to write a macro/vba script/event procedure, where would I put it? The "OnLoad" line? The "OnOpen"? OnCurrent? (Pardon me, I'm still a bit of a newbie).

Thanks very much.

 

[FancyPrairie]

It depends on when you want to apply the filter. If you want the form to always open with the filter assigned, then just add the filter string to the Filter property of the form. A filter is like a where clause without the word Where. If you are confused about it, open the query (in the query builer), assigned to the RecordSource property of the form and add your Where condition. Then view the query in SQL view and copy and paste the Where condition (without the Where word) to the Filter Property of the form.

You can apply the Filter command in the OnOpen if you want or whenever you want the filter to take effect.

 

[TechieJr]

Hello FancyPrairie,

I've been trying to follow your suggestions and keep coming up with all records showing instead of the few that should be showing.

I wrote a quick query to extract the proper records and it it works great as a query. When I copy the SQL code (sans the word "Where&quot into the "Filter" property on the form, it does nothing. (The form is based on a table, not a query.)

I've checked for misplaced/missing quotation marks, brackets, periods vs exclaimation marks, etc. and can't seem to figure out why the same code works when part of a query but not as part of the filter property of the form.

Any further ideas?

Thanks for your help. It must be monday. :-(

TechieJr.

 

[FancyPrairie]

Why don't you show me your code. Maybe I can see what's wrong.

 

[TechieJr]

Good Morning FancyPrairie,

I have a macro which runs in the background when a user enters a new record. It automatically adds a code to the "EntryForm" field in the table "t_Master". In this particular case, the form is "f_ClientManager" and the code is "CM".

What should happen is when the "f_ClientManager" form is openned by a user, all they should see are the records that were inputed from that form. I have tried the code below with various levels of brackets and it works in the test query, but not when the code is applied to the Filter property of the form.

 (((t_Master.EntryForm)="cm"))

The above code is the "unadulterated" SQL from the "Where" line of the test query. Hopes this helps explain what I'm trying to do better. Thanks again

 

[FancyPrairie]

I don't get it. Where is the field "EntryForm" being updated? A form? If so, which form?

I'm assuming the the form "f_ClientManager" is not open at the time a new record is added to the table "t_Master". If I'm right then, in the OnOpen event of "f_ClientManager" include the following code:

Me.Filter = "t_Master.EntryForm = 'cm'"
Me.FilterOn = True

 

[TechieJr]

The field "EntryForm" is hidden from the user and is not a visible part of the form. I am using the "AfterInsert" property of the "f_ClientManager" form to trigger a macro which sets the value of the "EntryForm" field in the underlying table "t_Master". The form "f_ClientManager" is based on the "t_Master" table and is what the user uses to enter the information for the new record, so it is open.

I'll try your suggestion again, but my property list for the form does not include a "FilterOn" area. Would I place your second line on the same line as your first but separated by some punctuation?

Does this explain things better? Thanks for your help.

 

[FancyPrairie]

First, I don't believe your users are going to see the addition for a while, depending on what you have the Refresh Interval set to (default is 1 minute) (see TOOLS|OPTIONS and select the Advanced Tab).

I'm guess I'm a little dense today, but I'm still confused as to what it is you are trying to do. Maybe there's another way of doing it. For example, generally the user enters something in a field and we filter or search based on the value they entered. Try to be more specific as to what it is you are trying to do.

 

[TechieJr]

Good News! :-D

Thanks to your help I found a way for the filter to work. Following your suggestions, I wrote a macro which would apply the filter I wanted and had it run on the "OnOpen" event for the form. After it ran the first time, I noticed that it wrote a line of code in the "Filter" property of the form. The code it wrote looked a lot like what came up in the SQL code you had me try earlier. The SQL code looked like this:

 (((t_Master.EntryForm)="cm"))

The code which worked looks like this:

 ([t_Master].[EntryForm])="cm"

I deleted the macro from the OnOpen event line and left the code in the filter line and it works like a charm.

Your last post mentioned that you wanted a better idea of what I am trying to do. I have a multi-purpose table into which records are entered through different forms. When a user views a record, it has to be seen through the same form from which it was entered. Each time a record is entered, a hidden macro is triggered which sets the value of a specific field to a code which identifies the form. When a form is opened, the records are filtered based on the value of that field and only those records entered from that form are shown.

I don't know if that helps you understand what I'm trying to do, but thanks to you I was able to make it work.

Thank you very much.