User permissions

  • Thread starter Thread starter Guest
  • Start date Start date
No, you wouldn't do that in the On Open event for the report. You'd do it
in the form where the command button is. The command button that opens your
report.
If faq_IsUserInGroup("Admins", CurrentUser) Then
doCmd.OpenReport "mileagegasmaintrpt,acpreview
Else
DoCmd.OpenReport "mileagegasmaintrpt",acpreview,,"username = " & chr(34) &
CurrentUser() & chr(34)
End If



--
Joan Wild
Microsoft Access MVP
Todd said:
I need clarification on this. As far as the SQL below all I need to do is
remove the last part of the statement: AND
((usernameregion,.username)=CurrentUser()))

After this I need to add the following code in the Report OnOpen Event:

DoCmd.OpenReport "mileagegasmaintrpt",acpreview,,"username = " & chr(34) &
CurrentUser() & chr(34)

How and where should the part about the DoCmd.OpenReport for the Admins
group be done?

--
Todd


Joan Wild said:
I would base the report on a query with just
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])
Click to expand...

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
My reports form is set up like this: I'm using an option group on the form
with the radio buttons where you would select a report and then select a
command button to either print preview or print (these are separate command
buttons). Each of these two buttons have the On Click event code tied to a
macro with all the reports set to either print or print preview. The macro
is the same macro for both command buttons.

What I did is obviously wrong but I added the code you just mentioned to the
On Open of the actual form where these reports are located. There isn't an
On Open event for the option groups or the command buttons for the print and
print preview

When I log in as a user and select the reports menu form it prompts me for a
user name (it's not suppose to do this), when I put the user name in there I
can get to the form. If I don't it gives me an error. Once in the form I
enter the start date and end date and select the report and click print
preview and it pulls up the blank report. Then I close the form and click on
it again and it opens with all regions data and not just the region I'm
logged in as.

When I log in as my admin account I can open the reports menu form without
having to enter a user name and when I pull up the same report it goes to the
blank report. I close the report and open it again and it opens with the
data this time.

Here is the data I inserted in the Report Menu Forms On Open Event:

If faq_IsUserInGroup("Admins", CurrentUser) Then
DoCmd.OpenReport "mileagegasmaintrpt", acPreview
Else
DoCmd.OpenReport "mileagegasmaintrpt", acPreview, , "username =" &
Chr(34) & CurrentUser() & Chr(34)
End If

And below is my query SQL:

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]
FROM vehiclelistingtbl INNER JOIN monthlydatatbl ON
vehiclelistingtbl.[Inventory #] = monthlydatatbl.[Inventory #]
WHERE ((monthlydatatbl.[Month/Day/Year]) Between
[Forms]![frmrptparam]![txtstartdate] And [Forms]![frmrptparam]![txtenddate]);


--
Todd


Joan Wild said:
No, you wouldn't do that in the On Open event for the report. You'd do it
in the form where the command button is. The command button that opens your
report.
If faq_IsUserInGroup("Admins", CurrentUser) Then
doCmd.OpenReport "mileagegasmaintrpt,acpreview
Else
DoCmd.OpenReport "mileagegasmaintrpt",acpreview,,"username = " & chr(34) &
CurrentUser() & chr(34)
End If



--
Joan Wild
Microsoft Access MVP
Todd said:
I need clarification on this. As far as the SQL below all I need to do is
remove the last part of the statement: AND
((usernameregion,.username)=CurrentUser()))

After this I need to add the following code in the Report OnOpen Event:

DoCmd.OpenReport "mileagegasmaintrpt",acpreview,,"username = " & chr(34) &
CurrentUser() & chr(34)

How and where should the part about the DoCmd.OpenReport for the Admins
group be done?

--
Todd


Joan Wild said:
I would base the report on a query with just
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])

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
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


:

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
Click to expand...
Click to expand...
Click to expand...
 
Todd said:
My reports form is set up like this: I'm using an option group on the
form
with the radio buttons where you would select a report and then select a
command button to either print preview or print (these are separate
command
buttons). Each of these two buttons have the On Click event code tied to
a
macro with all the reports set to either print or print preview. The
macro
is the same macro for both command buttons.
OK

What I did is obviously wrong but I added the code you just mentioned to
the
On Open of the actual form where these reports are located. There isn't
an
On Open event for the option groups or the command buttons for the print
and
print preview
Click to expand...

Wrong place, more below
When I log in as a user and select the reports menu form it prompts me for
a
user name (it's not suppose to do this), when I put the user name in there
I
can get to the form.
Click to expand...

It's doing this because you put code in this form's OnOpen event that refers
to UserName; the form doesn't know about Username, so it prompts you.
Remove what you have in the Open event for this form.
Here is the data I inserted in the Report Menu Forms On Open Event:
Click to expand...

No, no. You are trying to open the report (doCmd.OpenReport) in the
report's Open event. That makes no sense, and you can't this.

Can I suggest you lose the macros on the command buttons on the form to open
your reports and instead use code? In the Click event for the Print Preview
button on your form...
Select Case NameOfOptionGroup
Case 1
If faq_IsUserInGroup("Admins", CurrentUser) Then
DoCmd.OpenReport "mileagegasmaintrpt", acPreview
Else
DoCmd.OpenReport "mileagegasmaintrpt", acPreview, , "username =" &
Chr(34) & CurrentUser() & Chr(34)
End If
Case 2
DoCmd.OpenReport "some other report", acPreview
Case 3
etc
End Select

For the Print button...
Select Case NameOfOptionGroup
Case 1
If faq_IsUserInGroup("Admins", CurrentUser) Then
DoCmd.OpenReport "mileagegasmaintrpt"
Else
DoCmd.OpenReport "mileagegasmaintrpt", , , "username =" & Chr(34) &
CurrentUser() & Chr(34)
End If
Case 2
DoCmd.OpenReport "some other report"
Case 3
etc.
End Select

I usually use a little driver table for forms like this, however I don't
want to complicate this further for you.

--
Joan Wild
Microsoft Access MVP
And below is my query SQL:

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]
FROM vehiclelistingtbl INNER JOIN monthlydatatbl ON
vehiclelistingtbl.[Inventory #] = monthlydatatbl.[Inventory #]
WHERE ((monthlydatatbl.[Month/Day/Year]) Between
[Forms]![frmrptparam]![txtstartdate] And
[Forms]![frmrptparam]![txtenddate]);


--
Todd


Joan Wild said:
No, you wouldn't do that in the On Open event for the report. You'd do
it
in the form where the command button is. The command button that opens
your
report.
If faq_IsUserInGroup("Admins", CurrentUser) Then
doCmd.OpenReport "mileagegasmaintrpt,acpreview
Else
DoCmd.OpenReport "mileagegasmaintrpt",acpreview,,"username = " & chr(34)
&
CurrentUser() & chr(34)
End If



--
Joan Wild
Microsoft Access MVP
Todd said:
I need clarification on this. As far as the SQL below all I need to do
is
remove the last part of the statement: AND
((usernameregion,.username)=CurrentUser()))

After this I need to add the following code in the Report OnOpen Event:

DoCmd.OpenReport "mileagegasmaintrpt",acpreview,,"username = " &
chr(34) &
CurrentUser() & chr(34)

How and where should the part about the DoCmd.OpenReport for the Admins
group be done?

--
Todd


:

I would base the report on a query with just
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])

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
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


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top