Todd said:Here you go:
SELECT vehiclelistingtbl.[VIN #], vehiclelistingtbl.[Inventory #],
vehiclelistingtbl.[Tag #], vehiclelistingtbl.[Vehicle Type],
vehiclelistingtbl.[Vehicle Year], vehiclelistingtbl.Passenger,
vehiclelistingtbl.[Marked Vehicle], vehiclelistingtbl.Active,
vehiclelistingtbl.Division, vehiclelistingtbl.Location,
vehiclelistingtbl.assignedperson, vehiclelistingtbl.vehiclenotes,
usernameregion.region, usernameregion.username
FROM vehiclelistingtbl INNER JOIN usernameregion ON
vehiclelistingtbl.Location = usernameregion.region
WHERE (((vehiclelistingtbl.Active)=True) AND
((usernameregion.username)=CurrentUser()));
--
Todd
Joan Wild said:Open the vehicleregionqry query in design view and then choose View, SQL;
copy and paste that statement in a reply here.
Joan Wild said:Seems to me that the following will work...
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehiclelistingtbl"
Else
Me.RecordSource = "vehicleregionqry"
End If
End Sub
--
Joan Wild
Microsoft Access MVP
Todd said:Here you go:
SELECT vehiclelistingtbl.[VIN #], vehiclelistingtbl.[Inventory #],
vehiclelistingtbl.[Tag #], vehiclelistingtbl.[Vehicle Type],
vehiclelistingtbl.[Vehicle Year], vehiclelistingtbl.Passenger,
vehiclelistingtbl.[Marked Vehicle], vehiclelistingtbl.Active,
vehiclelistingtbl.Division, vehiclelistingtbl.Location,
vehiclelistingtbl.assignedperson, vehiclelistingtbl.vehiclenotes,
usernameregion.region, usernameregion.username
FROM vehiclelistingtbl INNER JOIN usernameregion ON
vehiclelistingtbl.Location = usernameregion.region
WHERE (((vehiclelistingtbl.Active)=True) AND
((usernameregion.username)=CurrentUser()));
--
Todd
Joan Wild said:Open the vehicleregionqry query in design view and then choose View, SQL;
copy and paste that statement in a reply here.
--
Joan Wild
Microsoft Access MVP
This is what I copied from the secfaq Item 22 into a module I named
usergroupidentifymodule:
Function faq_IsUserInGroup (strGroup As String, strUser as String) As
Integer
' Returns True if user is in group, False otherwise
' This only works if you're a member of the Admins group.
Dim ws As WorkSpace
Dim grp As Group
Dim strUserName as string
Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroup)
On Error Resume Next
strUserName = ws.groups(strGroup).users(strUser).Name
faq_IsUserInGroup = (Err = 0)
End Function
And this is what I entered in the Vehicle Form Event "On Open"
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehicleregionqry"
Else
Me.RecordSource = vehicleregionqry
End If
End Sub
It wouldn't let me enter the <> at the beginning and end of
Me.RecordSource
= <vehicleregionqry>. Should this be in it? The vehicleregionqry is my
recordsource for the vehicle form so that's why it is in the above code
twice. Should there be a different query there?
In my menu screen where the users select their region I've got 5
switchboard
options for Region 1-5 and each of them have different queries but with
the
same fields and all come from the same vehiclelistingtable. I entered
the
info you told me previously (user/group table) and added these field to
each
query and entered Currentuser() in the user criteria of each query.
This
did
work fine but as an admin user when I logged in and selected a region
to
open
I couldn't open any of them since I wasn't assigned to one of the
regions.
--
Todd
:
That function is available in the security FAQ. You can download it
from
http://support.microsoft.com/?id=207793
Put it in a standard module (give the module a different name than the
function)
--
Joan Wild
Microsoft Access MVP
When I entered the code you suggested below, it gave me an error of
"Sub
or
Function not defined". What should I put in the part of the code
faq_IsUserInGroup? The name of my table where it identifies the
users
and
region they belong to is usernameregion and the two fields in the
table
are
user and region.
--
Todd
:
You can use the open event for your form to set the recordsource...
If faq_IsUserInGroup("Admins",CurrentUser) then
Me.RecordSource = "SELECT * FROM YourQuery"
Else
Me.RecordSource = <the existing recordsource you have>
End If
If this doesn't work, post the recordsource that you currently are
using.
You do not need the combo since the recordsource is restricted to
'their'
region.
--
Joan Wild
Microsoft Access MVP
What should I do for myself and another user that I've given
admin
rights.
We are really not assigned a region so by making the changes you
suggested
we
can't open any of the regions via the front end (or exe). I'd
like
for
us
to
be able to open any of the regions through the exe?
The things you suggested to me work good. When the users try and
select
another region should it just go to a blank screen because it
does.
They
would just then need to click the close box and it would go back
to
the
previous menu screen.
--
Todd
:
I suggest you have a separate table with the Access username and
the
Region
they belong to.
Then in your queries and form recordsources you can add this
table
and
link
on the Region; put a criteria under the username of
CurrentUser().
--
Joan Wild
Microsoft Access MVP
Joan,
Piggybacking on the previous question and your response, if
one
of
my
groups
is the "Full Data User" group and I've got users assigned to
that
group,
how
can I restrict them from accessing certain forms once they are
logged
in.
EX: Each of my users are from a different region and I'd only
like
for
them
to access their regions information to enter/edit/delete data.
In
my
dbase
I've got command buttons (with a macro tied to each of them to
sort
and
open
only each specific regions data) where all users can select
their
region.
The problem is that they have the ability to select any of the
regions
if
they like.
Would I do this under the "users and group permissions" dialog
option?
I
did this for my region1 user as a test and selected only the
macro
that
I
wanted to allow region1 to have access too but it still let
them
in
the
other
regions even though I didn't select the other regions
openmacro.
Help!
--
Todd
:
The dialog box only shows explicit permissions, not implicit
(i.e.
those
inherited from group membership).
It is possible to assign permissions to users, however that
isn't
recommended as it's difficult to maintain.
--
Joan Wild
Microsoft Access MVP
in
message
When Users are assigned to specific User groups (ex: New
Data
Users),
when
the User and Group Permissions dialog box is viewed, only
the
permissions
of
the Groups are shown and not the individual users (ie: when
the
"Users"
bullet is checked)
Why is this?
Todd said:Joan,
That worked but it shows all the vehicles and in my query I've got the
criteria set to only show the active vehicles (true). As a user it only
shows the active ones but as Admin it shows all. Did this code override
what
the criteria was?
--
Todd
Joan Wild said:Seems to me that the following will work...
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehiclelistingtbl"
Else
Me.RecordSource = "vehicleregionqry"
End If
End Sub
--
Joan Wild
Microsoft Access MVP
Todd said:Here you go:
SELECT vehiclelistingtbl.[VIN #], vehiclelistingtbl.[Inventory #],
vehiclelistingtbl.[Tag #], vehiclelistingtbl.[Vehicle Type],
vehiclelistingtbl.[Vehicle Year], vehiclelistingtbl.Passenger,
vehiclelistingtbl.[Marked Vehicle], vehiclelistingtbl.Active,
vehiclelistingtbl.Division, vehiclelistingtbl.Location,
vehiclelistingtbl.assignedperson, vehiclelistingtbl.vehiclenotes,
usernameregion.region, usernameregion.username
FROM vehiclelistingtbl INNER JOIN usernameregion ON
vehiclelistingtbl.Location = usernameregion.region
WHERE (((vehiclelistingtbl.Active)=True) AND
((usernameregion.username)=CurrentUser()));
--
Todd
:
Open the vehicleregionqry query in design view and then choose View,
SQL;
copy and paste that statement in a reply here.
--
Joan Wild
Microsoft Access MVP
This is what I copied from the secfaq Item 22 into a module I named
usergroupidentifymodule:
Function faq_IsUserInGroup (strGroup As String, strUser as String)
As
Integer
' Returns True if user is in group, False otherwise
' This only works if you're a member of the Admins group.
Dim ws As WorkSpace
Dim grp As Group
Dim strUserName as string
Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroup)
On Error Resume Next
strUserName = ws.groups(strGroup).users(strUser).Name
faq_IsUserInGroup = (Err = 0)
End Function
And this is what I entered in the Vehicle Form Event "On Open"
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehicleregionqry"
Else
Me.RecordSource = vehicleregionqry
End If
End Sub
It wouldn't let me enter the <> at the beginning and end of
Me.RecordSource
= <vehicleregionqry>. Should this be in it? The vehicleregionqry is
my
recordsource for the vehicle form so that's why it is in the above
code
twice. Should there be a different query there?
In my menu screen where the users select their region I've got 5
switchboard
options for Region 1-5 and each of them have different queries but
with
the
same fields and all come from the same vehiclelistingtable. I
entered
the
info you told me previously (user/group table) and added these field
to
each
query and entered Currentuser() in the user criteria of each query.
This
did
work fine but as an admin user when I logged in and selected a
region
to
open
I couldn't open any of them since I wasn't assigned to one of the
regions.
--
Todd
:
That function is available in the security FAQ. You can download
it
from
http://support.microsoft.com/?id=207793
Put it in a standard module (give the module a different name than
the
function)
--
Joan Wild
Microsoft Access MVP
When I entered the code you suggested below, it gave me an error
of
"Sub
or
Function not defined". What should I put in the part of the code
faq_IsUserInGroup? The name of my table where it identifies the
users
and
region they belong to is usernameregion and the two fields in the
table
are
user and region.
--
Todd
:
You can use the open event for your form to set the
recordsource...
If faq_IsUserInGroup("Admins",CurrentUser) then
Me.RecordSource = "SELECT * FROM YourQuery"
Else
Me.RecordSource = <the existing recordsource you have>
End If
If this doesn't work, post the recordsource that you currently
are
using.
You do not need the combo since the recordsource is restricted
to
'their'
region.
--
Joan Wild
Microsoft Access MVP
What should I do for myself and another user that I've given
admin
rights.
We are really not assigned a region so by making the changes
you
suggested
we
can't open any of the regions via the front end (or exe). I'd
like
for
us
to
be able to open any of the regions through the exe?
The things you suggested to me work good. When the users try
and
select
another region should it just go to a blank screen because it
does.
They
would just then need to click the close box and it would go
back
to
the
previous menu screen.
--
Todd
:
I suggest you have a separate table with the Access username
and
the
Region
they belong to.
Then in your queries and form recordsources you can add this
table
and
link
on the Region; put a criteria under the username of
CurrentUser().
--
Joan Wild
Microsoft Access MVP
Joan,
Piggybacking on the previous question and your response, if
one
of
my
groups
is the "Full Data User" group and I've got users assigned
to
that
group,
how
can I restrict them from accessing certain forms once they
are
logged
in.
EX: Each of my users are from a different region and I'd
only
like
for
them
to access their regions information to enter/edit/delete
data.
In
my
dbase
I've got command buttons (with a macro tied to each of them
to
sort
and
open
only each specific regions data) where all users can select
their
region.
The problem is that they have the ability to select any of
the
regions
if
they like.
Would I do this under the "users and group permissions"
dialog
option?
I
did this for my region1 user as a test and selected only
the
macro
that
I
wanted to allow region1 to have access too but it still let
them
in
the
other
regions even though I didn't select the other regions
openmacro.
Help!
--
Todd
:
The dialog box only shows explicit permissions, not
implicit
(i.e.
those
inherited from group membership).
It is possible to assign permissions to users, however
that
isn't
recommended as it's difficult to maintain.
--
Joan Wild
Microsoft Access MVP
"fennellkevin" <[email protected]>
wrote
in
message
When Users are assigned to specific User groups (ex: New
Data
Users),
when
the User and Group Permissions dialog box is viewed,
only
the
permissions
of
the Groups are shown and not the individual users (ie:
when
the
"Users"
bullet is checked)
Why is this?
Joan Wild said:The statement for the Admins Group is
SELECT * FROM vehiclelistingtbl
That's why it's returning all records. You need to adjust the statement by
including the WHERE clause
SELECT * FROM vehiclelistingtbl WHERE Active=True
If you design a query using the QBE grid that pulls what you want, you can
switch to SQL View and use that SQL statement in your code.
--
Joan Wild
Microsoft Access MVP
Todd said:Joan,
That worked but it shows all the vehicles and in my query I've got the
criteria set to only show the active vehicles (true). As a user it only
shows the active ones but as Admin it shows all. Did this code override
what
the criteria was?
--
Todd
Joan Wild said:Seems to me that the following will work...
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehiclelistingtbl"
Else
Me.RecordSource = "vehicleregionqry"
End If
End Sub
--
Joan Wild
Microsoft Access MVP
Here you go:
SELECT vehiclelistingtbl.[VIN #], vehiclelistingtbl.[Inventory #],
vehiclelistingtbl.[Tag #], vehiclelistingtbl.[Vehicle Type],
vehiclelistingtbl.[Vehicle Year], vehiclelistingtbl.Passenger,
vehiclelistingtbl.[Marked Vehicle], vehiclelistingtbl.Active,
vehiclelistingtbl.Division, vehiclelistingtbl.Location,
vehiclelistingtbl.assignedperson, vehiclelistingtbl.vehiclenotes,
usernameregion.region, usernameregion.username
FROM vehiclelistingtbl INNER JOIN usernameregion ON
vehiclelistingtbl.Location = usernameregion.region
WHERE (((vehiclelistingtbl.Active)=True) AND
((usernameregion.username)=CurrentUser()));
--
Todd
:
Open the vehicleregionqry query in design view and then choose View,
SQL;
copy and paste that statement in a reply here.
--
Joan Wild
Microsoft Access MVP
This is what I copied from the secfaq Item 22 into a module I named
usergroupidentifymodule:
Function faq_IsUserInGroup (strGroup As String, strUser as String)
As
Integer
' Returns True if user is in group, False otherwise
' This only works if you're a member of the Admins group.
Dim ws As WorkSpace
Dim grp As Group
Dim strUserName as string
Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroup)
On Error Resume Next
strUserName = ws.groups(strGroup).users(strUser).Name
faq_IsUserInGroup = (Err = 0)
End Function
And this is what I entered in the Vehicle Form Event "On Open"
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehicleregionqry"
Else
Me.RecordSource = vehicleregionqry
End If
End Sub
It wouldn't let me enter the <> at the beginning and end of
Me.RecordSource
= <vehicleregionqry>. Should this be in it? The vehicleregionqry is
my
recordsource for the vehicle form so that's why it is in the above
code
twice. Should there be a different query there?
In my menu screen where the users select their region I've got 5
switchboard
options for Region 1-5 and each of them have different queries but
with
the
same fields and all come from the same vehiclelistingtable. I
entered
the
info you told me previously (user/group table) and added these field
to
each
query and entered Currentuser() in the user criteria of each query.
This
did
work fine but as an admin user when I logged in and selected a
region
to
open
I couldn't open any of them since I wasn't assigned to one of the
regions.
--
Todd
:
That function is available in the security FAQ. You can download
it
from
http://support.microsoft.com/?id=207793
Put it in a standard module (give the module a different name than
the
function)
--
Joan Wild
Microsoft Access MVP
When I entered the code you suggested below, it gave me an error
of
"Sub
or
Function not defined". What should I put in the part of the code
faq_IsUserInGroup? The name of my table where it identifies the
users
and
region they belong to is usernameregion and the two fields in the
table
are
user and region.
--
Todd
:
You can use the open event for your form to set the
recordsource...
If faq_IsUserInGroup("Admins",CurrentUser) then
Me.RecordSource = "SELECT * FROM YourQuery"
Else
Me.RecordSource = <the existing recordsource you have>
End If
If this doesn't work, post the recordsource that you currently
are
using.
You do not need the combo since the recordsource is restricted
to
'their'
region.
--
Joan Wild
Microsoft Access MVP
What should I do for myself and another user that I've given
admin
rights.
We are really not assigned a region so by making the changes
you
suggested
we
can't open any of the regions via the front end (or exe). I'd
like
for
us
to
be able to open any of the regions through the exe?
The things you suggested to me work good. When the users try
and
select
another region should it just go to a blank screen because it
does.
They
would just then need to click the close box and it would go
back
to
the
previous menu screen.
--
Todd
:
I suggest you have a separate table with the Access username
and
the
Region
they belong to.
Then in your queries and form recordsources you can add this
table
and
link
on the Region; put a criteria under the username of
CurrentUser().
--
Joan Wild
Microsoft Access MVP
Joan,
Piggybacking on the previous question and your response, if
one
of
my
groups
is the "Full Data User" group and I've got users assigned
to
that
group,
how
can I restrict them from accessing certain forms once they
are
logged
in.
EX: Each of my users are from a different region and I'd
only
like
for
them
to access their regions information to enter/edit/delete
data.
In
my
dbase
I've got command buttons (with a macro tied to each of them
to
sort
and
open
only each specific regions data) where all users can select
their
region.
The problem is that they have the ability to select any of
the
regions
if
they like.
Would I do this under the "users and group permissions"
dialog
option?
I
did this for my region1 user as a test and selected only
the
macro
that
I
wanted to allow region1 to have access too but it still let
them
in
the
other
regions even though I didn't select the other regions
openmacro.
Help!
Todd said:One other question on this subject. How can I make the same restrictions
work for viewing reports? On my reports form I've got date ranges (start
and
end date) in two text fields and a dropdown combobox where they can select
their region or leave blank to display all regions. Can I somehow limit
their report availablity to only their regions information?
The leaving blank is good for the admin so they don't have to select each
region separately.
--
Todd
Joan Wild said:The statement for the Admins Group is
SELECT * FROM vehiclelistingtbl
That's why it's returning all records. You need to adjust the statement
by
including the WHERE clause
SELECT * FROM vehiclelistingtbl WHERE Active=True
If you design a query using the QBE grid that pulls what you want, you
can
switch to SQL View and use that SQL statement in your code.
--
Joan Wild
Microsoft Access MVP
Todd said:Joan,
That worked but it shows all the vehicles and in my query I've got the
criteria set to only show the active vehicles (true). As a user it
only
shows the active ones but as Admin it shows all. Did this code
override
what
the criteria was?
--
Todd
:
Seems to me that the following will work...
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehiclelistingtbl"
Else
Me.RecordSource = "vehicleregionqry"
End If
End Sub
--
Joan Wild
Microsoft Access MVP
Here you go:
SELECT vehiclelistingtbl.[VIN #], vehiclelistingtbl.[Inventory #],
vehiclelistingtbl.[Tag #], vehiclelistingtbl.[Vehicle Type],
vehiclelistingtbl.[Vehicle Year], vehiclelistingtbl.Passenger,
vehiclelistingtbl.[Marked Vehicle], vehiclelistingtbl.Active,
vehiclelistingtbl.Division, vehiclelistingtbl.Location,
vehiclelistingtbl.assignedperson, vehiclelistingtbl.vehiclenotes,
usernameregion.region, usernameregion.username
FROM vehiclelistingtbl INNER JOIN usernameregion ON
vehiclelistingtbl.Location = usernameregion.region
WHERE (((vehiclelistingtbl.Active)=True) AND
((usernameregion.username)=CurrentUser()));
--
Todd
:
Open the vehicleregionqry query in design view and then choose
View,
SQL;
copy and paste that statement in a reply here.
--
Joan Wild
Microsoft Access MVP
This is what I copied from the secfaq Item 22 into a module I
named
usergroupidentifymodule:
Function faq_IsUserInGroup (strGroup As String, strUser as
String)
As
Integer
' Returns True if user is in group, False otherwise
' This only works if you're a member of the Admins group.
Dim ws As WorkSpace
Dim grp As Group
Dim strUserName as string
Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroup)
On Error Resume Next
strUserName = ws.groups(strGroup).users(strUser).Name
faq_IsUserInGroup = (Err = 0)
End Function
And this is what I entered in the Vehicle Form Event "On Open"
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehicleregionqry"
Else
Me.RecordSource = vehicleregionqry
End If
End Sub
It wouldn't let me enter the <> at the beginning and end of
Me.RecordSource
= <vehicleregionqry>. Should this be in it? The vehicleregionqry
is
my
recordsource for the vehicle form so that's why it is in the
above
code
twice. Should there be a different query there?
In my menu screen where the users select their region I've got 5
switchboard
options for Region 1-5 and each of them have different queries
but
with
the
same fields and all come from the same vehiclelistingtable. I
entered
the
info you told me previously (user/group table) and added these
field
to
each
query and entered Currentuser() in the user criteria of each
query.
This
did
work fine but as an admin user when I logged in and selected a
region
to
open
I couldn't open any of them since I wasn't assigned to one of the
regions.
--
Todd
:
That function is available in the security FAQ. You can
download
it
from
http://support.microsoft.com/?id=207793
Put it in a standard module (give the module a different name
than
the
function)
--
Joan Wild
Microsoft Access MVP
When I entered the code you suggested below, it gave me an
error
of
"Sub
or
Function not defined". What should I put in the part of the
code
faq_IsUserInGroup? The name of my table where it identifies
the
users
and
region they belong to is usernameregion and the two fields in
the
table
are
user and region.
--
Todd
:
You can use the open event for your form to set the
recordsource...
If faq_IsUserInGroup("Admins",CurrentUser) then
Me.RecordSource = "SELECT * FROM YourQuery"
Else
Me.RecordSource = <the existing recordsource you have>
End If
If this doesn't work, post the recordsource that you
currently
are
using.
You do not need the combo since the recordsource is
restricted
to
'their'
region.
--
Joan Wild
Microsoft Access MVP
What should I do for myself and another user that I've
given
admin
rights.
We are really not assigned a region so by making the
changes
you
suggested
we
can't open any of the regions via the front end (or exe).
I'd
like
for
us
to
be able to open any of the regions through the exe?
The things you suggested to me work good. When the users
try
and
select
another region should it just go to a blank screen because
it
does.
They
would just then need to click the close box and it would go
back
to
the
previous menu screen.
--
Todd
:
I suggest you have a separate table with the Access
username
and
the
Region
they belong to.
Then in your queries and form recordsources you can add
this
table
and
link
on the Region; put a criteria under the username of
CurrentUser().
--
Joan Wild
Microsoft Access MVP
Joan,
Piggybacking on the previous question and your response,
if
one
of
my
groups
is the "Full Data User" group and I've got users
assigned
to
that
group,
how
can I restrict them from accessing certain forms once
they
are
logged
in.
EX: Each of my users are from a different region and I'd
only
like
for
them
to access their regions information to enter/edit/delete
data.
In
my
dbase
I've got command buttons (with a macro tied to each of
them
to
sort
and
open
only each specific regions data) where all users can
select
their
region.
The problem is that they have the ability to select any
of
the
regions
if
they like.
Would I do this under the "users and group permissions"
dialog
option?
I
did this for my region1 user as a test and selected only
the
macro
that
I
wanted to allow region1 to have access too but it still
let
them
in
the
other
regions even though I didn't select the other regions
openmacro.
Help!
Joan Wild said:Todd said:One other question on this subject. How can I make the same restrictions
work for viewing reports? On my reports form I've got date ranges (start
and
end date) in two text fields and a dropdown combobox where they can select
their region or leave blank to display all regions. Can I somehow limit
their report availablity to only their regions information?
The leaving blank is good for the admin so they don't have to select each
region separately.
Use the same principle. Change the recordsource of the report and then open
it. You don't want a combobox (I don't think), since users are restricted
to their region anyway.
Reports!rptName.RecordSource = "Select...etc"
and for the users
Reports!rptName.RecordSource = "a query that limits based on the currentuser
--
Todd
Joan Wild said:The statement for the Admins Group is
SELECT * FROM vehiclelistingtbl
That's why it's returning all records. You need to adjust the statement
by
including the WHERE clause
SELECT * FROM vehiclelistingtbl WHERE Active=True
If you design a query using the QBE grid that pulls what you want, you
can
switch to SQL View and use that SQL statement in your code.
--
Joan Wild
Microsoft Access MVP
Joan,
That worked but it shows all the vehicles and in my query I've got the
criteria set to only show the active vehicles (true). As a user it
only
shows the active ones but as Admin it shows all. Did this code
override
what
the criteria was?
--
Todd
:
Seems to me that the following will work...
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehiclelistingtbl"
Else
Me.RecordSource = "vehicleregionqry"
End If
End Sub
--
Joan Wild
Microsoft Access MVP
Here you go:
SELECT vehiclelistingtbl.[VIN #], vehiclelistingtbl.[Inventory #],
vehiclelistingtbl.[Tag #], vehiclelistingtbl.[Vehicle Type],
vehiclelistingtbl.[Vehicle Year], vehiclelistingtbl.Passenger,
vehiclelistingtbl.[Marked Vehicle], vehiclelistingtbl.Active,
vehiclelistingtbl.Division, vehiclelistingtbl.Location,
vehiclelistingtbl.assignedperson, vehiclelistingtbl.vehiclenotes,
usernameregion.region, usernameregion.username
FROM vehiclelistingtbl INNER JOIN usernameregion ON
vehiclelistingtbl.Location = usernameregion.region
WHERE (((vehiclelistingtbl.Active)=True) AND
((usernameregion.username)=CurrentUser()));
--
Todd
:
Open the vehicleregionqry query in design view and then choose
View,
SQL;
copy and paste that statement in a reply here.
--
Joan Wild
Microsoft Access MVP
This is what I copied from the secfaq Item 22 into a module I
named
usergroupidentifymodule:
Function faq_IsUserInGroup (strGroup As String, strUser as
String)
As
Integer
' Returns True if user is in group, False otherwise
' This only works if you're a member of the Admins group.
Dim ws As WorkSpace
Dim grp As Group
Dim strUserName as string
Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroup)
On Error Resume Next
strUserName = ws.groups(strGroup).users(strUser).Name
faq_IsUserInGroup = (Err = 0)
End Function
And this is what I entered in the Vehicle Form Event "On Open"
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehicleregionqry"
Else
Me.RecordSource = vehicleregionqry
End If
End Sub
It wouldn't let me enter the <> at the beginning and end of
Me.RecordSource
= <vehicleregionqry>. Should this be in it? The vehicleregionqry
is
my
recordsource for the vehicle form so that's why it is in the
above
code
twice. Should there be a different query there?
In my menu screen where the users select their region I've got 5
switchboard
options for Region 1-5 and each of them have different queries
but
with
the
same fields and all come from the same vehiclelistingtable. I
entered
the
info you told me previously (user/group table) and added these
field
to
each
query and entered Currentuser() in the user criteria of each
query.
This
did
work fine but as an admin user when I logged in and selected a
region
to
open
I couldn't open any of them since I wasn't assigned to one of the
regions.
--
Todd
:
That function is available in the security FAQ. You can
download
it
from
http://support.microsoft.com/?id=207793
Put it in a standard module (give the module a different name
than
the
function)
--
Joan Wild
Microsoft Access MVP
When I entered the code you suggested below, it gave me an
error
of
"Sub
or
Function not defined". What should I put in the part of the
code
faq_IsUserInGroup? The name of my table where it identifies
the
users
and
region they belong to is usernameregion and the two fields in
the
table
are
user and region.
--
Todd
:
You can use the open event for your form to set the
recordsource...
If faq_IsUserInGroup("Admins",CurrentUser) then
Me.RecordSource = "SELECT * FROM YourQuery"
Else
Me.RecordSource = <the existing recordsource you have>
End If
If this doesn't work, post the recordsource that you
currently
are
using.
You do not need the combo since the recordsource is
restricted
to
'their'
region.
--
Joan Wild
Microsoft Access MVP
What should I do for myself and another user that I've
given
admin
rights.
We are really not assigned a region so by making the
changes
you
suggested
we
can't open any of the regions via the front end (or exe).
I'd
like
for
us
to
be able to open any of the regions through the exe?
The things you suggested to me work good. When the users
try
and
select
another region should it just go to a blank screen because
it
does.
They
would just then need to click the close box and it would go
back
to
the
previous menu screen.
--
Todd
:
I suggest you have a separate table with the Access
username
and
the
Region
they belong to.
Then in your queries and form recordsources you can add
this
table
and
link
on the Region; put a criteria under the username of
CurrentUser().
--
Joan Wild
Microsoft Access MVP
Joan,
Piggybacking on the previous question and your response,
if
one
of
SELECT vehiclelistingtbl.Location, vehiclelistingtbl.[Inventory #],
vehiclelistingtbl.[Tag #], vehiclelistingtbl.[Vehicle Type],
vehiclelistingtbl.[VIN #], monthlydatatbl.[Inventory #],
monthlydatatbl.[Month/Day/Year], monthlydatatbl.[Date Assigned],
monthlydatatbl.[Vehicle Usage], monthlydatatbl.[Beginning Mileage],
monthlydatatbl.[Ending Mileage], monthlydatatbl.[Miles Driven],
monthlydatatbl.Gallons, monthlydatatbl.Price, monthlydatatbl.[Oil Change],
monthlydatatbl.oilchangeotherpymt, monthlydatatbl.Wash,
monthlydatatbl.washotherpymt, monthlydatatbl.repaircosts,
monthlydatatbl.repaircostsotherpymt, monthlydatatbl.[Repair Description],
monthlydatatbl.inspectstickercost, monthlydatatbl.inspectstickerotherpymt,
vehiclelistingtbl.[Vehicle Year], usernameregion.username,
usernameregion.region
FROM (vehiclelistingtbl INNER JOIN usernameregion ON
vehiclelistingtbl.Location = usernameregion.region) INNER JOIN
monthlydatatbl
ON vehiclelistingtbl.[Inventory #] = monthlydatatbl.[Inventory #];
WHERE (((monthlydatatbl.[Month/Day/Year]) Between
[Forms]![frmrptparam]![txtstartdate] And
[Forms]![frmrptparam]![txtenddate])
Todd said:I did the same thing as what we earlier covered by adding the userregiontbl
in the rptqry and linking on the region and I added the code in the onopen
event of the report to give Admin people the view of all records since
they
are not in a region. It works for the users but the admin part isn't
working. When I open the report it prompts me for parameters for things
in
an additional table I have in the query.
This report query has an additional table (monthlydatatbl) in it and I'll
bet that's the problem. Should I also reference this table in the On Open
event in the report for the admin users? If so, how should I include the
monthlydatatbl in the OnOpen event?
Below is my query SQL data:
SELECT vehiclelistingtbl.Location, vehiclelistingtbl.[Inventory #],
vehiclelistingtbl.[Tag #], vehiclelistingtbl.[Vehicle Type],
vehiclelistingtbl.[VIN #], monthlydatatbl.[Inventory #],
monthlydatatbl.[Month/Day/Year], monthlydatatbl.[Date Assigned],
monthlydatatbl.[Vehicle Usage], monthlydatatbl.[Beginning Mileage],
monthlydatatbl.[Ending Mileage], monthlydatatbl.[Miles Driven],
monthlydatatbl.Gallons, monthlydatatbl.Price, monthlydatatbl.[Oil Change],
monthlydatatbl.oilchangeotherpymt, monthlydatatbl.Wash,
monthlydatatbl.washotherpymt, monthlydatatbl.repaircosts,
monthlydatatbl.repaircostsotherpymt, monthlydatatbl.[Repair Description],
monthlydatatbl.inspectstickercost, monthlydatatbl.inspectstickerotherpymt,
vehiclelistingtbl.[Vehicle Year], usernameregion.username,
usernameregion.region
FROM (vehiclelistingtbl INNER JOIN usernameregion ON
vehiclelistingtbl.Location = usernameregion.region) INNER JOIN
monthlydatatbl
ON vehiclelistingtbl.[Inventory #] = monthlydatatbl.[Inventory #]
WHERE (((monthlydatatbl.[Month/Day/Year]) Between
[Forms]![frmrptparam]![txtstartdate] And
[Forms]![frmrptparam]![txtenddate])
AND ((usernameregion.username)=CurrentUser()));
--
Todd
Joan Wild said:Todd said:One other question on this subject. How can I make the same
restrictions
work for viewing reports? On my reports form I've got date ranges
(start
and
end date) in two text fields and a dropdown combobox where they can
select
their region or leave blank to display all regions. Can I somehow
limit
their report availablity to only their regions information?
The leaving blank is good for the admin so they don't have to select
each
region separately.
Use the same principle. Change the recordsource of the report and then
open
it. You don't want a combobox (I don't think), since users are
restricted
to their region anyway.
Reports!rptName.RecordSource = "Select...etc"
and for the users
Reports!rptName.RecordSource = "a query that limits based on the
currentuser
--
Todd
:
The statement for the Admins Group is
SELECT * FROM vehiclelistingtbl
That's why it's returning all records. You need to adjust the
statement
by
including the WHERE clause
SELECT * FROM vehiclelistingtbl WHERE Active=True
If you design a query using the QBE grid that pulls what you want, you
can
switch to SQL View and use that SQL statement in your code.
--
Joan Wild
Microsoft Access MVP
Joan,
That worked but it shows all the vehicles and in my query I've got
the
criteria set to only show the active vehicles (true). As a user it
only
shows the active ones but as Admin it shows all. Did this code
override
what
the criteria was?
--
Todd
:
Seems to me that the following will work...
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehiclelistingtbl"
Else
Me.RecordSource = "vehicleregionqry"
End If
End Sub
--
Joan Wild
Microsoft Access MVP
Here you go:
SELECT vehiclelistingtbl.[VIN #], vehiclelistingtbl.[Inventory
#],
vehiclelistingtbl.[Tag #], vehiclelistingtbl.[Vehicle Type],
vehiclelistingtbl.[Vehicle Year], vehiclelistingtbl.Passenger,
vehiclelistingtbl.[Marked Vehicle], vehiclelistingtbl.Active,
vehiclelistingtbl.Division, vehiclelistingtbl.Location,
vehiclelistingtbl.assignedperson, vehiclelistingtbl.vehiclenotes,
usernameregion.region, usernameregion.username
FROM vehiclelistingtbl INNER JOIN usernameregion ON
vehiclelistingtbl.Location = usernameregion.region
WHERE (((vehiclelistingtbl.Active)=True) AND
((usernameregion.username)=CurrentUser()));
--
Todd
:
Open the vehicleregionqry query in design view and then choose
View,
SQL;
copy and paste that statement in a reply here.
--
Joan Wild
Microsoft Access MVP
This is what I copied from the secfaq Item 22 into a module I
named
usergroupidentifymodule:
Function faq_IsUserInGroup (strGroup As String, strUser as
String)
As
Integer
' Returns True if user is in group, False otherwise
' This only works if you're a member of the Admins group.
Dim ws As WorkSpace
Dim grp As Group
Dim strUserName as string
Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroup)
On Error Resume Next
strUserName = ws.groups(strGroup).users(strUser).Name
faq_IsUserInGroup = (Err = 0)
End Function
And this is what I entered in the Vehicle Form Event "On Open"
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehicleregionqry"
Else
Me.RecordSource = vehicleregionqry
End If
End Sub
It wouldn't let me enter the <> at the beginning and end of
Me.RecordSource
= <vehicleregionqry>. Should this be in it? The
vehicleregionqry
is
my
recordsource for the vehicle form so that's why it is in the
above
code
twice. Should there be a different query there?
In my menu screen where the users select their region I've got
5
switchboard
options for Region 1-5 and each of them have different queries
but
with
the
same fields and all come from the same vehiclelistingtable. I
entered
the
info you told me previously (user/group table) and added these
field
to
each
query and entered Currentuser() in the user criteria of each
query.
This
did
work fine but as an admin user when I logged in and selected a
region
to
open
I couldn't open any of them since I wasn't assigned to one of
the
regions.
--
Todd
:
That function is available in the security FAQ. You can
download
it
from
http://support.microsoft.com/?id=207793
Put it in a standard module (give the module a different name
than
the
function)
--
Joan Wild
Microsoft Access MVP
When I entered the code you suggested below, it gave me an
error
of
"Sub
or
Function not defined". What should I put in the part of
the
code
faq_IsUserInGroup? The name of my table where it
identifies
the
users
and
region they belong to is usernameregion and the two fields
in
the
table
are
user and region.
--
Todd
:
You can use the open event for your form to set the
recordsource...
If faq_IsUserInGroup("Admins",CurrentUser) then
Me.RecordSource = "SELECT * FROM YourQuery"
Else
Me.RecordSource = <the existing recordsource you have>
End If
If this doesn't work, post the recordsource that you
currently
are
using.
You do not need the combo since the recordsource is
restricted
to
'their'
region.
--
Joan Wild
Microsoft Access MVP
What should I do for myself and another user that I've
given
admin
rights.
We are really not assigned a region so by making the
changes
you
suggested
we
can't open any of the regions via the front end (or
exe).
I'd
like
for
us
to
be able to open any of the regions through the exe?
The things you suggested to me work good. When the
users
try
and
select
another region should it just go to a blank screen
because
it
does.
They
would just then need to click the close box and it would
go
back
to
the
previous menu screen.
--
Todd
:
I suggest you have a separate table with the Access
username
and
the
Region
they belong to.
Then in your queries and form recordsources you can add
this
table
and
link
on the Region; put a criteria under the username of
CurrentUser().
--
Joan Wild
Microsoft Access MVP
Joan,
Piggybacking on the previous question and your
response,
if
one
of
Joan Wild said:I would base the report on a query with justSELECT vehiclelistingtbl.Location, vehiclelistingtbl.[Inventory #],
vehiclelistingtbl.[Tag #], vehiclelistingtbl.[Vehicle Type],
vehiclelistingtbl.[VIN #], monthlydatatbl.[Inventory #],
monthlydatatbl.[Month/Day/Year], monthlydatatbl.[Date Assigned],
monthlydatatbl.[Vehicle Usage], monthlydatatbl.[Beginning Mileage],
monthlydatatbl.[Ending Mileage], monthlydatatbl.[Miles Driven],
monthlydatatbl.Gallons, monthlydatatbl.Price, monthlydatatbl.[Oil Change],
monthlydatatbl.oilchangeotherpymt, monthlydatatbl.Wash,
monthlydatatbl.washotherpymt, monthlydatatbl.repaircosts,
monthlydatatbl.repaircostsotherpymt, monthlydatatbl.[Repair Description],
monthlydatatbl.inspectstickercost, monthlydatatbl.inspectstickerotherpymt,
vehiclelistingtbl.[Vehicle Year], usernameregion.username,
usernameregion.region
FROM (vehiclelistingtbl INNER JOIN usernameregion ON
vehiclelistingtbl.Location = usernameregion.region) INNER JOIN
monthlydatatbl
ON vehiclelistingtbl.[Inventory #] = monthlydatatbl.[Inventory #];
WHERE (((monthlydatatbl.[Month/Day/Year]) Between
[Forms]![frmrptparam]![txtstartdate] And
[Forms]![frmrptparam]![txtenddate])
You can then use
Docmd.OpenReport
for the Admins group and
DoCmd.OpenReport "reportname",acpreview,,"username = " & chr(34) &
CurrrentUser() & chr(34)
for the rest.
--
Joan Wild
Microsoft Access MVP
Todd said:I did the same thing as what we earlier covered by adding the userregiontbl
in the rptqry and linking on the region and I added the code in the onopen
event of the report to give Admin people the view of all records since
they
are not in a region. It works for the users but the admin part isn't
working. When I open the report it prompts me for parameters for things
in
an additional table I have in the query.
This report query has an additional table (monthlydatatbl) in it and I'll
bet that's the problem. Should I also reference this table in the On Open
event in the report for the admin users? If so, how should I include the
monthlydatatbl in the OnOpen event?
Below is my query SQL data:
SELECT vehiclelistingtbl.Location, vehiclelistingtbl.[Inventory #],
vehiclelistingtbl.[Tag #], vehiclelistingtbl.[Vehicle Type],
vehiclelistingtbl.[VIN #], monthlydatatbl.[Inventory #],
monthlydatatbl.[Month/Day/Year], monthlydatatbl.[Date Assigned],
monthlydatatbl.[Vehicle Usage], monthlydatatbl.[Beginning Mileage],
monthlydatatbl.[Ending Mileage], monthlydatatbl.[Miles Driven],
monthlydatatbl.Gallons, monthlydatatbl.Price, monthlydatatbl.[Oil Change],
monthlydatatbl.oilchangeotherpymt, monthlydatatbl.Wash,
monthlydatatbl.washotherpymt, monthlydatatbl.repaircosts,
monthlydatatbl.repaircostsotherpymt, monthlydatatbl.[Repair Description],
monthlydatatbl.inspectstickercost, monthlydatatbl.inspectstickerotherpymt,
vehiclelistingtbl.[Vehicle Year], usernameregion.username,
usernameregion.region
FROM (vehiclelistingtbl INNER JOIN usernameregion ON
vehiclelistingtbl.Location = usernameregion.region) INNER JOIN
monthlydatatbl
ON vehiclelistingtbl.[Inventory #] = monthlydatatbl.[Inventory #]
WHERE (((monthlydatatbl.[Month/Day/Year]) Between
[Forms]![frmrptparam]![txtstartdate] And
[Forms]![frmrptparam]![txtenddate])
AND ((usernameregion.username)=CurrentUser()));
--
Todd
Joan Wild said:One other question on this subject. How can I make the same
restrictions
work for viewing reports? On my reports form I've got date ranges
(start
and
end date) in two text fields and a dropdown combobox where they can
select
their region or leave blank to display all regions. Can I somehow
limit
their report availablity to only their regions information?
The leaving blank is good for the admin so they don't have to select
each
region separately.
Use the same principle. Change the recordsource of the report and then
open
it. You don't want a combobox (I don't think), since users are
restricted
to their region anyway.
Reports!rptName.RecordSource = "Select...etc"
and for the users
Reports!rptName.RecordSource = "a query that limits based on the
currentuser
--
Todd
:
The statement for the Admins Group is
SELECT * FROM vehiclelistingtbl
That's why it's returning all records. You need to adjust the
statement
by
including the WHERE clause
SELECT * FROM vehiclelistingtbl WHERE Active=True
If you design a query using the QBE grid that pulls what you want, you
can
switch to SQL View and use that SQL statement in your code.
--
Joan Wild
Microsoft Access MVP
Joan,
That worked but it shows all the vehicles and in my query I've got
the
criteria set to only show the active vehicles (true). As a user it
only
shows the active ones but as Admin it shows all. Did this code
override
what
the criteria was?
--
Todd
:
Seems to me that the following will work...
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehiclelistingtbl"
Else
Me.RecordSource = "vehicleregionqry"
End If
End Sub
--
Joan Wild
Microsoft Access MVP
Here you go:
SELECT vehiclelistingtbl.[VIN #], vehiclelistingtbl.[Inventory
#],
vehiclelistingtbl.[Tag #], vehiclelistingtbl.[Vehicle Type],
vehiclelistingtbl.[Vehicle Year], vehiclelistingtbl.Passenger,
vehiclelistingtbl.[Marked Vehicle], vehiclelistingtbl.Active,
vehiclelistingtbl.Division, vehiclelistingtbl.Location,
vehiclelistingtbl.assignedperson, vehiclelistingtbl.vehiclenotes,
usernameregion.region, usernameregion.username
FROM vehiclelistingtbl INNER JOIN usernameregion ON
vehiclelistingtbl.Location = usernameregion.region
WHERE (((vehiclelistingtbl.Active)=True) AND
((usernameregion.username)=CurrentUser()));
--
Todd
:
Open the vehicleregionqry query in design view and then choose
View,
SQL;
copy and paste that statement in a reply here.
--
Joan Wild
Microsoft Access MVP
This is what I copied from the secfaq Item 22 into a module I
named
usergroupidentifymodule:
Function faq_IsUserInGroup (strGroup As String, strUser as
String)
As
Integer
' Returns True if user is in group, False otherwise
' This only works if you're a member of the Admins group.
Dim ws As WorkSpace
Dim grp As Group
Dim strUserName as string
Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroup)
On Error Resume Next
strUserName = ws.groups(strGroup).users(strUser).Name
faq_IsUserInGroup = (Err = 0)
End Function
And this is what I entered in the Vehicle Form Event "On Open"
Private Sub Form_Open(Cancel As Integer)
If faq_IsUserInGroup("Admins", CurrentUser) Then
Me.RecordSource = "SELECT * FROM vehicleregionqry"
Else
Me.RecordSource = vehicleregionqry
End If
End Sub
It wouldn't let me enter the <> at the beginning and end of
Me.RecordSource
= <vehicleregionqry>. Should this be in it? The
vehicleregionqry
is
my
recordsource for the vehicle form so that's why it is in the
above
code
twice. Should there be a different query there?
In my menu screen where the users select their region I've got
5
switchboard
options for Region 1-5 and each of them have different queries
but
with
the
same fields and all come from the same vehiclelistingtable. I
entered
the
info you told me previously (user/group table) and added these
field
to
each
query and entered Currentuser() in the user criteria of each
query.
This
did
work fine but as an admin user when I logged in and selected a
region
to
open
I couldn't open any of them since I wasn't assigned to one of
the
regions.
--
Todd
:
That function is available in the security FAQ. You can
download
it
from
http://support.microsoft.com/?id=207793
Put it in a standard module (give the module a different name
than
the
function)
--
Joan Wild
Microsoft Access MVP
When I entered the code you suggested below, it gave me an
error
of
"Sub
or
Function not defined". What should I put in the part of
the
code
faq_IsUserInGroup? The name of my table where it
identifies
the
users
and
region they belong to is usernameregion and the two fields
in
the