special permissions on database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a database with user level security for a group of five
supervisors, and their manager. The database is configured so each
supervisor is directed to their own form. The CurrentUser fucntion is
employed so each supervisor has the ability to look at their work only.
However, the manager's custom form has a combo box so he can look at his work
and the work of his subordinates. It is not working right now because as the
queries are designed for CurrentUser, the manager can only look at his own
work. I already added the manager to the Admins group. Can I give the
manager the ability to look at other's work without creating new queries and
macros? Thank you.
 
brem219 said:
I created a database with user level security for a group of five
supervisors, and their manager. The database is configured so each
supervisor is directed to their own form. The CurrentUser fucntion is
employed so each supervisor has the ability to look at their work
only.
Click to expand...

You can use a single form to accomplish this - no need for a separate form
for each supervisor.
However, the manager's custom form has a combo box so he can
look at his work and the work of his subordinates. It is not working
right now because as the queries are designed for CurrentUser, the
manager can only look at his own work. I already added the manager
to the Admins group. Can I give the manager the ability to look at
other's work without creating new queries and macros? Thank you.
Click to expand...

You can use one form and in it's open event set the recordsource property
for the form:
There's code in the security FAQ you can use to determine if the current
user is a member of a group. Put that function in a module (give the module
a differnent name than the function).
Then use it like...

If faqIsUserInGroup("Admins", CurrentUser()) then
Me.RecordSource = "SELECT * FROM SomeTable"
else
Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " & chr(34) &
CurrentUser() & chr(34)
end if

Watch for newsreader wrap in the above
 
Joan Wild said:
You can use a single form to accomplish this - no need for a separate form
for each supervisor.


You can use one form and in it's open event set the recordsource property
for the form:
There's code in the security FAQ you can use to determine if the current
user is a member of a group. Put that function in a module (give the module
a differnent name than the function).
Then use it like...

If faqIsUserInGroup("Admins", CurrentUser()) then
Me.RecordSource = "SELECT * FROM SomeTable"
else
Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " & chr(34) &
CurrentUser() & chr(34)
end if

Watch for newsreader wrap in the above
Click to expand...
 
I am new to code, functions, and modules and don’t know much about how to use
them. I entered the following SQL code that I found in the Security FAQ
under the record source on the main form in a module I called PullUser:
Function faq_IsUserInGroup (strGroup As String, strUser as String) As Integer
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(strUsers).Name
faq_IsUserInGroup = (Err = 0)
End Function

Next I tried to place this SQL code in the record source of the Main form:

If faqIsUserInGroup(“Adminsâ€,CurrentUser()) then
Me.RecordSource = “SELECT * FROM Main Tableâ€
Else
Me.RecordSource = “SELECT * FROM SomeTable WHERE [Username] = “& CurrentUser()
End If

It kept rejecting the code, saying the SQL statement was invalid. Where
should this be placed? Where does the module come into play? I hope you can
help me with this.
 
Undo that.

Go to the Modules tab, and create a new Module, paste the Function there.
Save and close the module and call it
basUtilities.

Open your form in design view. Go to the On Open event property and select
[Event Procedure] in the drop down for that property. Then click on the
build button (...) to the right. This will open a window with
Private Sub Form_Open(Cancel As Integer)

End Sub

Between those two lines paste the If statement.

Go to the debug menu, and choose compile. Then close the window and save
your form.

--
Joan Wild
Microsoft Access MVP
I am new to code, functions, and modules and don't know much about
how to use them. I entered the following SQL code that I found in
the Security FAQ under the record source on the main form in a module
I called PullUser: Function faq_IsUserInGroup (strGroup As String,
strUser as String) As Integer 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(strUsers).Name
faq_IsUserInGroup = (Err = 0)
End Function

Next I tried to place this SQL code in the record source of the Main
form:

If faqIsUserInGroup("Admins",CurrentUser()) then
Me.RecordSource = "SELECT * FROM Main Table"
Else
Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
CurrentUser() End If

It kept rejecting the code, saying the SQL statement was invalid.
Where should this be placed? Where does the module come into play?
I hope you can help me with this.




Click to expand...
 
Thank you for your reply. I did as you asked, and I am getting the following
error:

Run-time error '3131':
Syntax error in FROM clause.

I open up the code and the following statement is highlighted:

Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
CurrentUser()

The name of the table is Main Table. Username is the field where the
CurrentUser is identified.

Joan Wild said:
Undo that.

Go to the Modules tab, and create a new Module, paste the Function there.
Save and close the module and call it
basUtilities.

Open your form in design view. Go to the On Open event property and select
[Event Procedure] in the drop down for that property. Then click on the
build button (...) to the right. This will open a window with
Private Sub Form_Open(Cancel As Integer)

End Sub

Between those two lines paste the If statement.

Go to the debug menu, and choose compile. Then close the window and save
your form.

--
Joan Wild
Microsoft Access MVP
I am new to code, functions, and modules and don't know much about
how to use them. I entered the following SQL code that I found in
the Security FAQ under the record source on the main form in a module
I called PullUser: Function faq_IsUserInGroup (strGroup As String,
strUser as String) As Integer 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(strUsers).Name
faq_IsUserInGroup = (Err = 0)
End Function

Next I tried to place this SQL code in the record source of the Main
form:

If faqIsUserInGroup("Admins",CurrentUser()) then
Me.RecordSource = "SELECT * FROM Main Table"
Else
Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
CurrentUser() End If

It kept rejecting the code, saying the SQL statement was invalid.
Where should this be placed? Where does the module come into play?
I hope you can help me with this.




:

brem219 wrote:
I created a database with user level security for a group of five
supervisors, and their manager. The database is configured so each
supervisor is directed to their own form. The CurrentUser
fucntion is employed so each supervisor has the ability to look at
their work only.

You can use a single form to accomplish this - no need for a
separate form for each supervisor.

However, the manager's custom form has a combo box so he can
look at his work and the work of his subordinates. It is not
working right now because as the queries are designed for
CurrentUser, the manager can only look at his own work. I already
added the manager to the Admins group. Can I give the manager the
ability to look at other's work without creating new queries and
macros? Thank you.

You can use one form and in it's open event set the recordsource
property for the form:
There's code in the security FAQ you can use to determine if the
current user is a member of a group. Put that function in a module
(give the module a differnent name than the function).
Then use it like...

If faqIsUserInGroup("Admins", CurrentUser()) then
Me.RecordSource = "SELECT * FROM SomeTable"
else
Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
chr(34) & CurrentUser() & chr(34)
end if

Watch for newsreader wrap in the above
Click to expand...
Click to expand...
Click to expand...
 
Enclose Main Table is square brackets:

....FROM [Main Table] WHERE...

Also I believe you need to change it to
WHERE [Username] = " & chr(34) & CurrentUser() & chr(34)

to put quotes around the string that CurrentUser() returns.

I also assume that it's all on one line, and it just appears to be two due
to newsreader wrap?

--
Joan Wild
Microsoft Access MVP
Thank you for your reply. I did as you asked, and I am getting the
following error:

Run-time error '3131':
Syntax error in FROM clause.

I open up the code and the following statement is highlighted:

Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
CurrentUser()

The name of the table is Main Table. Username is the field where the
CurrentUser is identified.

Joan Wild said:
Undo that.

Go to the Modules tab, and create a new Module, paste the Function
there. Save and close the module and call it
basUtilities.

Open your form in design view. Go to the On Open event property and
select [Event Procedure] in the drop down for that property. Then
click on the build button (...) to the right. This will open a
window with
Private Sub Form_Open(Cancel As Integer)

End Sub

Between those two lines paste the If statement.

Go to the debug menu, and choose compile. Then close the window and
save your form.

--
Joan Wild
Microsoft Access MVP
I am new to code, functions, and modules and don't know much about
how to use them. I entered the following SQL code that I found in
the Security FAQ under the record source on the main form in a
module I called PullUser: Function faq_IsUserInGroup (strGroup As
String, strUser as String) As Integer 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(strUsers).Name
faq_IsUserInGroup = (Err = 0)
End Function

Next I tried to place this SQL code in the record source of the Main
form:

If faqIsUserInGroup("Admins",CurrentUser()) then
Me.RecordSource = "SELECT * FROM Main Table"
Else
Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
CurrentUser() End If

It kept rejecting the code, saying the SQL statement was invalid.
Where should this be placed? Where does the module come into play?
I hope you can help me with this.







:

brem219 wrote:
I created a database with user level security for a group of five
supervisors, and their manager. The database is configured so
each supervisor is directed to their own form. The CurrentUser
fucntion is employed so each supervisor has the ability to look
at their work only.

You can use a single form to accomplish this - no need for a
separate form for each supervisor.

However, the manager's custom form has a combo box so he can
look at his work and the work of his subordinates. It is not
working right now because as the queries are designed for
CurrentUser, the manager can only look at his own work. I
already added the manager to the Admins group. Can I give the
manager the ability to look at other's work without creating new
queries and macros? Thank you.

You can use one form and in it's open event set the recordsource
property for the form:
There's code in the security FAQ you can use to determine if the
current user is a member of a group. Put that function in a
module (give the module a differnent name than the function).
Then use it like...

If faqIsUserInGroup("Admins", CurrentUser()) then
Me.RecordSource = "SELECT * FROM SomeTable"
else
Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
chr(34) & CurrentUser() & chr(34)
end if

Watch for newsreader wrap in the above
Click to expand...
Click to expand...
Click to expand...
 
Okay, now the line of code looks like this, and it's all on one line:

Me.RecordSource = "SELECT * FROM [Main Table] WHERE [Username]= " & chr(34)
CurrentUser() & chr(34)

However, now I am getting an error that highlights CurrentUser and reads
"Expected: end of statement" Do I need a semicolon or comma anywhere?
Thanks again for your help.

Joan Wild said:
Enclose Main Table is square brackets:

....FROM [Main Table] WHERE...

Also I believe you need to change it to
WHERE [Username] = " & chr(34) & CurrentUser() & chr(34)

to put quotes around the string that CurrentUser() returns.

I also assume that it's all on one line, and it just appears to be two due
to newsreader wrap?

--
Joan Wild
Microsoft Access MVP
Thank you for your reply. I did as you asked, and I am getting the
following error:

Run-time error '3131':
Syntax error in FROM clause.

I open up the code and the following statement is highlighted:

Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
CurrentUser()

The name of the table is Main Table. Username is the field where the
CurrentUser is identified.

Joan Wild said:
Undo that.

Go to the Modules tab, and create a new Module, paste the Function
there. Save and close the module and call it
basUtilities.

Open your form in design view. Go to the On Open event property and
select [Event Procedure] in the drop down for that property. Then
click on the build button (...) to the right. This will open a
window with
Private Sub Form_Open(Cancel As Integer)

End Sub

Between those two lines paste the If statement.

Go to the debug menu, and choose compile. Then close the window and
save your form.

--
Joan Wild
Microsoft Access MVP

brem219 wrote:
I am new to code, functions, and modules and don't know much about
how to use them. I entered the following SQL code that I found in
the Security FAQ under the record source on the main form in a
module I called PullUser: Function faq_IsUserInGroup (strGroup As
String, strUser as String) As Integer 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(strUsers).Name
faq_IsUserInGroup = (Err = 0)
End Function

Next I tried to place this SQL code in the record source of the Main
form:

If faqIsUserInGroup("Admins",CurrentUser()) then
Me.RecordSource = "SELECT * FROM Main Table"
Else
Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
CurrentUser() End If

It kept rejecting the code, saying the SQL statement was invalid.
Where should this be placed? Where does the module come into play?
I hope you can help me with this.







:

brem219 wrote:
I created a database with user level security for a group of five
supervisors, and their manager. The database is configured so
each supervisor is directed to their own form. The CurrentUser
fucntion is employed so each supervisor has the ability to look
at their work only.

You can use a single form to accomplish this - no need for a
separate form for each supervisor.

However, the manager's custom form has a combo box so he can
look at his work and the work of his subordinates. It is not
working right now because as the queries are designed for
CurrentUser, the manager can only look at his own work. I
already added the manager to the Admins group. Can I give the
manager the ability to look at other's work without creating new
queries and macros? Thank you.

You can use one form and in it's open event set the recordsource
property for the form:
There's code in the security FAQ you can use to determine if the
current user is a member of a group. Put that function in a
module (give the module a differnent name than the function).
Then use it like...

If faqIsUserInGroup("Admins", CurrentUser()) then
Me.RecordSource = "SELECT * FROM SomeTable"
else
Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
chr(34) & CurrentUser() & chr(34)
end if

Watch for newsreader wrap in the above
Click to expand...
Click to expand...
Click to expand...
 
Okay, now the VBA code looks like this, and it's all on one line:

Me.RecordSource = "SELECT * FROM [Main Table] WHERE [Username]= " & chr(34)
CurrentUser() & chr(34)

Now I get an error that highlights CurrentUser and says "Expected: end of
statement" Do I need a comma or semicolon somewhere?

Thanks for your help.


Joan Wild said:
Enclose Main Table is square brackets:

....FROM [Main Table] WHERE...

Also I believe you need to change it to
WHERE [Username] = " & chr(34) & CurrentUser() & chr(34)

to put quotes around the string that CurrentUser() returns.

I also assume that it's all on one line, and it just appears to be two due
to newsreader wrap?

--
Joan Wild
Microsoft Access MVP
Thank you for your reply. I did as you asked, and I am getting the
following error:

Run-time error '3131':
Syntax error in FROM clause.

I open up the code and the following statement is highlighted:

Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
CurrentUser()

The name of the table is Main Table. Username is the field where the
CurrentUser is identified.

Joan Wild said:
Undo that.

Go to the Modules tab, and create a new Module, paste the Function
there. Save and close the module and call it
basUtilities.

Open your form in design view. Go to the On Open event property and
select [Event Procedure] in the drop down for that property. Then
click on the build button (...) to the right. This will open a
window with
Private Sub Form_Open(Cancel As Integer)

End Sub

Between those two lines paste the If statement.

Go to the debug menu, and choose compile. Then close the window and
save your form.

--
Joan Wild
Microsoft Access MVP

brem219 wrote:
I am new to code, functions, and modules and don't know much about
how to use them. I entered the following SQL code that I found in
the Security FAQ under the record source on the main form in a
module I called PullUser: Function faq_IsUserInGroup (strGroup As
String, strUser as String) As Integer 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(strUsers).Name
faq_IsUserInGroup = (Err = 0)
End Function

Next I tried to place this SQL code in the record source of the Main
form:

If faqIsUserInGroup("Admins",CurrentUser()) then
Me.RecordSource = "SELECT * FROM Main Table"
Else
Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
CurrentUser() End If

It kept rejecting the code, saying the SQL statement was invalid.
Where should this be placed? Where does the module come into play?
I hope you can help me with this.







:

brem219 wrote:
I created a database with user level security for a group of five
supervisors, and their manager. The database is configured so
each supervisor is directed to their own form. The CurrentUser
fucntion is employed so each supervisor has the ability to look
at their work only.

You can use a single form to accomplish this - no need for a
separate form for each supervisor.

However, the manager's custom form has a combo box so he can
look at his work and the work of his subordinates. It is not
working right now because as the queries are designed for
CurrentUser, the manager can only look at his own work. I
already added the manager to the Admins group. Can I give the
manager the ability to look at other's work without creating new
queries and macros? Thank you.

You can use one form and in it's open event set the recordsource
property for the form:
There's code in the security FAQ you can use to determine if the
current user is a member of a group. Put that function in a
module (give the module a differnent name than the function).
Then use it like...

If faqIsUserInGroup("Admins", CurrentUser()) then
Me.RecordSource = "SELECT * FROM SomeTable"
else
Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
chr(34) & CurrentUser() & chr(34)
end if

Watch for newsreader wrap in the above
Click to expand...
Click to expand...
Click to expand...
 
Okay, now the VBA code looks like this, and it's all on one line:

Me.RecordSource = "SELECT * FROM [Main Table] WHERE [Username]= " & chr(34)
CurrentUser() & chr(34)

Now I get an error that highlights CurrentUser and says "Expected: end of
statement" Do I need a comma or semicolon somewhere?

Thanks for your help.


Joan Wild said:
Enclose Main Table is square brackets:

....FROM [Main Table] WHERE...

Also I believe you need to change it to
WHERE [Username] = " & chr(34) & CurrentUser() & chr(34)

to put quotes around the string that CurrentUser() returns.

I also assume that it's all on one line, and it just appears to be two due
to newsreader wrap?

--
Joan Wild
Microsoft Access MVP
Thank you for your reply. I did as you asked, and I am getting the
following error:

Run-time error '3131':
Syntax error in FROM clause.

I open up the code and the following statement is highlighted:

Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
CurrentUser()

The name of the table is Main Table. Username is the field where the
CurrentUser is identified.

Joan Wild said:
Undo that.

Go to the Modules tab, and create a new Module, paste the Function
there. Save and close the module and call it
basUtilities.

Open your form in design view. Go to the On Open event property and
select [Event Procedure] in the drop down for that property. Then
click on the build button (...) to the right. This will open a
window with
Private Sub Form_Open(Cancel As Integer)

End Sub

Between those two lines paste the If statement.

Go to the debug menu, and choose compile. Then close the window and
save your form.

--
Joan Wild
Microsoft Access MVP

brem219 wrote:
I am new to code, functions, and modules and don't know much about
how to use them. I entered the following SQL code that I found in
the Security FAQ under the record source on the main form in a
module I called PullUser: Function faq_IsUserInGroup (strGroup As
String, strUser as String) As Integer 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(strUsers).Name
faq_IsUserInGroup = (Err = 0)
End Function

Next I tried to place this SQL code in the record source of the Main
form:

If faqIsUserInGroup("Admins",CurrentUser()) then
Me.RecordSource = "SELECT * FROM Main Table"
Else
Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
CurrentUser() End If

It kept rejecting the code, saying the SQL statement was invalid.
Where should this be placed? Where does the module come into play?
I hope you can help me with this.







:

brem219 wrote:
I created a database with user level security for a group of five
supervisors, and their manager. The database is configured so
each supervisor is directed to their own form. The CurrentUser
fucntion is employed so each supervisor has the ability to look
at their work only.

You can use a single form to accomplish this - no need for a
separate form for each supervisor.

However, the manager's custom form has a combo box so he can
look at his work and the work of his subordinates. It is not
working right now because as the queries are designed for
CurrentUser, the manager can only look at his own work. I
already added the manager to the Admins group. Can I give the
manager the ability to look at other's work without creating new
queries and macros? Thank you.

You can use one form and in it's open event set the recordsource
property for the form:
There's code in the security FAQ you can use to determine if the
current user is a member of a group. Put that function in a
module (give the module a differnent name than the function).
Then use it like...

If faqIsUserInGroup("Admins", CurrentUser()) then
Me.RecordSource = "SELECT * FROM SomeTable"
else
Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
chr(34) & CurrentUser() & chr(34)
end if

Watch for newsreader wrap in the above
Click to expand...
Click to expand...
Click to expand...
 
Okay, now the VBA code looks like this, and it's all on one line:

Me.RecordSource = "SELECT * FROM [Main Table] WHERE [Username]= " & chr(34)
CurrentUser() & chr(34)

Now I get an error that highlights CurrentUser and says "Expected: end of
statement" Do I need a comma or semicolon somewhere?

Thanks for your help.


Joan Wild said:
Enclose Main Table is square brackets:

....FROM [Main Table] WHERE...

Also I believe you need to change it to
WHERE [Username] = " & chr(34) & CurrentUser() & chr(34)

to put quotes around the string that CurrentUser() returns.

I also assume that it's all on one line, and it just appears to be two due
to newsreader wrap?

--
Joan Wild
Microsoft Access MVP
Thank you for your reply. I did as you asked, and I am getting the
following error:

Run-time error '3131':
Syntax error in FROM clause.

I open up the code and the following statement is highlighted:

Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
CurrentUser()

The name of the table is Main Table. Username is the field where the
CurrentUser is identified.

Joan Wild said:
Undo that.

Go to the Modules tab, and create a new Module, paste the Function
there. Save and close the module and call it
basUtilities.

Open your form in design view. Go to the On Open event property and
select [Event Procedure] in the drop down for that property. Then
click on the build button (...) to the right. This will open a
window with
Private Sub Form_Open(Cancel As Integer)

End Sub

Between those two lines paste the If statement.

Go to the debug menu, and choose compile. Then close the window and
save your form.

--
Joan Wild
Microsoft Access MVP

brem219 wrote:
I am new to code, functions, and modules and don't know much about
how to use them. I entered the following SQL code that I found in
the Security FAQ under the record source on the main form in a
module I called PullUser: Function faq_IsUserInGroup (strGroup As
String, strUser as String) As Integer 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(strUsers).Name
faq_IsUserInGroup = (Err = 0)
End Function

Next I tried to place this SQL code in the record source of the Main
form:

If faqIsUserInGroup("Admins",CurrentUser()) then
Me.RecordSource = "SELECT * FROM Main Table"
Else
Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
CurrentUser() End If

It kept rejecting the code, saying the SQL statement was invalid.
Where should this be placed? Where does the module come into play?
I hope you can help me with this.







:

brem219 wrote:
I created a database with user level security for a group of five
supervisors, and their manager. The database is configured so
each supervisor is directed to their own form. The CurrentUser
fucntion is employed so each supervisor has the ability to look
at their work only.

You can use a single form to accomplish this - no need for a
separate form for each supervisor.

However, the manager's custom form has a combo box so he can
look at his work and the work of his subordinates. It is not
working right now because as the queries are designed for
CurrentUser, the manager can only look at his own work. I
already added the manager to the Admins group. Can I give the
manager the ability to look at other's work without creating new
queries and macros? Thank you.

You can use one form and in it's open event set the recordsource
property for the form:
There's code in the security FAQ you can use to determine if the
current user is a member of a group. Put that function in a
module (give the module a differnent name than the function).
Then use it like...

If faqIsUserInGroup("Admins", CurrentUser()) then
Me.RecordSource = "SELECT * FROM SomeTable"
else
Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
chr(34) & CurrentUser() & chr(34)
end if

Watch for newsreader wrap in the above
Click to expand...
Click to expand...
Click to expand...
 
Sorry my mistake
....[Username] = " & chr(34) & CurrentUser() & chr(34)

missing a & before CurrentUser


--
Joan Wild
Microsoft Access MVP
Okay, now the line of code looks like this, and it's all on one line:

Me.RecordSource = "SELECT * FROM [Main Table] WHERE [Username]= " &
chr(34) CurrentUser() & chr(34)

However, now I am getting an error that highlights CurrentUser and
reads "Expected: end of statement" Do I need a semicolon or comma
anywhere? Thanks again for your help.

Joan Wild said:
Enclose Main Table is square brackets:

....FROM [Main Table] WHERE...

Also I believe you need to change it to
WHERE [Username] = " & chr(34) & CurrentUser() & chr(34)

to put quotes around the string that CurrentUser() returns.

I also assume that it's all on one line, and it just appears to be
two due to newsreader wrap?

--
Joan Wild
Microsoft Access MVP
Thank you for your reply. I did as you asked, and I am getting the
following error:

Run-time error '3131':
Syntax error in FROM clause.

I open up the code and the following statement is highlighted:

Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
CurrentUser()

The name of the table is Main Table. Username is the field where
the CurrentUser is identified.

:

Undo that.

Go to the Modules tab, and create a new Module, paste the Function
there. Save and close the module and call it
basUtilities.

Open your form in design view. Go to the On Open event property
and select [Event Procedure] in the drop down for that property.
Then click on the build button (...) to the right. This will open
a window with
Private Sub Form_Open(Cancel As Integer)

End Sub

Between those two lines paste the If statement.

Go to the debug menu, and choose compile. Then close the window
and save your form.

--
Joan Wild
Microsoft Access MVP

brem219 wrote:
I am new to code, functions, and modules and don't know much about
how to use them. I entered the following SQL code that I found in
the Security FAQ under the record source on the main form in a
module I called PullUser: Function faq_IsUserInGroup (strGroup As
String, strUser as String) As Integer 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(strUsers).Name
faq_IsUserInGroup = (Err = 0)
End Function

Next I tried to place this SQL code in the record source of the
Main form:

If faqIsUserInGroup("Admins",CurrentUser()) then
Me.RecordSource = "SELECT * FROM Main Table"
Else
Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
CurrentUser() End If

It kept rejecting the code, saying the SQL statement was invalid.
Where should this be placed? Where does the module come into
play? I hope you can help me with this.







:

brem219 wrote:
I created a database with user level security for a group of
five supervisors, and their manager. The database is
configured so each supervisor is directed to their own form.
The CurrentUser fucntion is employed so each supervisor has
the ability to look at their work only.

You can use a single form to accomplish this - no need for a
separate form for each supervisor.

However, the manager's custom form has a combo box so he can
look at his work and the work of his subordinates. It is not
working right now because as the queries are designed for
CurrentUser, the manager can only look at his own work. I
already added the manager to the Admins group. Can I give the
manager the ability to look at other's work without creating
new queries and macros? Thank you.

You can use one form and in it's open event set the recordsource
property for the form:
There's code in the security FAQ you can use to determine if the
current user is a member of a group. Put that function in a
module (give the module a differnent name than the function).
Then use it like...

If faqIsUserInGroup("Admins", CurrentUser()) then
Me.RecordSource = "SELECT * FROM SomeTable"
else
Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
chr(34) & CurrentUser() & chr(34)
end if

Watch for newsreader wrap in the above
Click to expand...
Click to expand...
Click to expand...
 
OK, I'm trying to use a popup form to identify the user entering into a
field. I've got a field named [QA] that I want to secure against anyone
without the correct password. Only Qualified Quality personnel are allowed
to enter information here. It's kind of like a virtual stamp. We use an
actual stamp now. I'm trying to re-create the log electronically. Anyhow,
when the user attempts to enter the field [QA] this event procedure is fired:

Private Sub QA_Enter()
DoCmd.OpenForm "frmEnterPassword", acNormal, , , , , "[QA]"
End Sub

This opens up a form which is supposed to verify that the password entered
matches the UserName of the person entering it. If they match, the UserName
is passed on. If they don't, the form just hangs there. It's triggered by
clicking on the CloseForm button. Here's the code:

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

If Len(Me.UserName) = 0 Or Len(Me.txtPassword) = 0 Then Exit Sub
strPassword = DLookup("[Password]", "Users", [UserName] = Me.UserName)
If strPassword = Me.txtPassword Then
Forms![HT Load Info].Controls(Me.OpenArgs) = Me.UserName
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub
End If

Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

It only kind of, sort of works... Depending on which record is being
entered into, sometimes the password for the previous record will work while
the current user password won't. I thought perhaps my cavalier use of the
name UserName was causing some confusion, but I changed the name in all of
code and the textbox without positive results. Perhaps the big brains at
this forum can help?
 
Back
Top