Help using form filters

[Jeff]

Hi,

I am trying to use vb code to set the filter on a form so that only
people with a common property can see each other on a form when they
open it (like if they are in the same department). But I cannot seam to
get the filters to work correctly and most of the time I'm just getting
no records to display.

Could someone lead me onto the right path?

[Larry Linson]

Possibly someone could make a useful suggestion if they knew just a little
more detail of what you have, and what you are trying to accomplish. Could
you clarify?

From whence do you determine the "affinity" or "value" for the "filter"?
"Filtering" can be accomplished in multiple ways, one of which is to create
a "WHERE clause without the WHERE" for use as the WhereCondtion argument of
a DoCmd.OpenForm statement. In many cases, however, I prefer to create the
entire SQL statement, and pass it to a newly-opened form in the OpenArgs
argument, then set it as the Form's RecordSource in the Open Event of the
Form.

Larry Linson
Microsoft Access MVP


"Jeff" <je**************@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

Hi,

I am trying to use vb code to set the filter on a form so that only
people with a common property can see each other on a form when they
open it (like if they are in the same department). But I cannot seam to
get the filters to work correctly and most of the time I'm just getting
no records to display.

Could someone lead me onto the right path?

[Jeff]

Ok, This is what I have. I have information for about 200 people in a
database, from several different departments. Its all pretty basic
stuff, like contact info and pay rate and such. So when someone logs
into the database they need to see a limited number of records based on
their position (or database rights). For instance, a department manager
needs to be able to see their employees, but not the employees of a
different department. And I want people to be able to login and update
their own information without them being able to see other people's
records.

I was trying to accomplish this using the form property filter, but I
can't seem to get it to work. To be more specific, on Form_Load() I had
code that looked up the current user's permissions, and department.
Then I set the filter as follows if they were the dept manager:

Me.Filter = "WHERE tblEmployee.deptID='" & userDept & "'"
Me.FilterOn

And like if they did not have database rights:

Me.Filter = "WHERE tblEmployee.userName='" & currentUser & "'"
Me.FilterOn

So, is there a better way to do what I am trying to accomplish, or am I
just missing some small detail that is keeping this from working?

Thanks for you help in advance.