Open forms with filter on certain field's value

[Guest]

Hi,

Is there any way i can open a form and filter out certain records with a
particular value in a field?

We have a staff database which we store current staff as well as staff who
have left us. However we must keep these records for legal purposes, however
they do need to be viewed, edited using their original form. Therefore what
I would like to do is filter out these staff when we open certain forms based
on the value of their Staff type Field.

This field is marked as inactive when any staff member leaves which takes
them out of any current reports but leaves their details in historic reports.

Ian

 

[Allen Browne]

Method 1:
Set the Filter of the form to:
"Inactive = False"
and set FilterOn to True.

Method 2:
Or set the RecordSource of the form to a query statement that only returns
the records you want, e.g.:
SELECT * FROM Staff WHERE Inactive = False;

Use Method 1 if you want users to be easily able to go back to ex-staff
(release the filter button on the toolbar), or use Method 2 if you don't
want that.

 

[SusanV]

Another option, if you're opening the form via a button:

DoCmd.OpenForm "YourForm",acNormal,,"WHERE StaffType <> Left"

(or whatever the form / field / value is)

 

[Guest]

Two possibilities.
1. Filter the form on the Staff Type field in form design view. Those with
the excluded code will not be seen.
2. Use a query as the Record Source for the form that is filtered on that
field rather than the table itself.

 

[Guest]

Hi,

Option 1 is my preferred method, only I tried entering the code that you
give me with no result, I must be doing something wrong. Sorry I'm not much
got with this sort of stuff.

Thanks for your help

Ian

 

[Allen Browne]

If the code goes into the form's module, it would be:
Me.Filiter = "Inactive = False"
Me.FilterOn = True

 

[Guest]

How do I put the code in to the forms module? Do I use

Before update, On insert etc?

Ian

 

[Allen Browne]

When would you like to do it?

When the form opens? Use the Open event procedure of the form.

When you click a button? Use the Click event procedure of the command
button.