Creating fields that only certain groups can change 1

svegal · Feb 16, 2000

I am new to creating anything complicated in Access. I need to create a database of drawing numbers. The database will have fields for approvals by several different groups. Only members of the group should be able to write to that group's approval field. 
 
I found a thread (Jan 4, 2000 - Jan 5, 2000) by Sterling, dougp, and Elisabeth that discussed this but I could not see a complete solution. 
 
I am thinking I can use the user/group level security and have a table for each groups approval (which only that group can change). Then link(?) each groups approval table to a table with all the drawing numbers, I tried putting a autonumber field in each table and link the autonumber fields together in a form, but the autonumbers seem to not work well (sometime it skips a number). 
 
How do I link(?) the tables together when they don't have a common field? 
 
Is there a better apporach than I am taking? 
 
Any help would be greatly appreciated. Thanks.

elizabeth · Feb 16, 2000

You don't link autonumbers to autonumbers, you link primary keys to foriegn keys. And when the primary key is an autonumber, you set it up as a long integer in the table that you are linking it to. 
 
For example, if you have: 
tblDrawing: primary key DrawingID (autonumber) 
tblApproval: primary key ApprovalID (autonumber), foriegn key DrawingID (long int). Link: tblDrawing.Drawing ID (one) to tblApproval.DrawingID (many). 
 
Don't worry about the Autonumbers skipping (not being sequential). Only the proper group would have add, delete or edit permissions on tblApproval, everyone else would just have read permissions.

WP · Feb 16, 2000

Sounds a bit complicated to me. 
 
Why not just lock the fields that are to be read-only for a certain user group or groups. 
 
Below is the code: 
 
Notice that I have made read-only field blue on a grey background. This looks good on sunken fields. 
 
 
If glrIsMember(CurrentUser(), "TheGroup&quot

Then 
Field1.Locked=True 
Field1.Forecolor=vbBlue 
Field1.Backcolor=cGrey 
 
Field1.Locked=True 
Field1.Forecolor=vbBlue 
Field1.Backcolor=cGrey 
 
etc. 
Endif 
 
 
 
Global Const cGrey = 12632256 
 
Function glrIsMember(ByVal pstrUser As String, ByVal pstrGroup As String) As Integer 
 
' Check if a user account is a member of a group account. 
' 
' (From The Microsoft Access Developer's Handbook 
' by Ken Getz, Paul Litwin, and Greg Reddick, Sybex 1994) 
' 
' In: 
' pstrName: name of user account 
' pstrGroup: name of group account 
' Out: 
' Return value: True (is member) or False (not a 
' member or either account doesn't exist). 
' Example: 
' intIsMember = glrIsMember("Alicia", "Librarians&quot

On Error GoTo glrIsMemberErr 
 
Dim wrk As Workspace 
Dim usr As User 
Dim gru As Group 
Dim strMsg As String 
Dim intErrHndlrFlag As Integer 
Dim varGroupName As Variant 
Dim strProcName As String 
 
Const FLAG_SET_USER = 1 
Const FLAG_SET_GROUP = 2 
Const FLAG_CHK_MEMBER = 4 
Const FLAG_ELSE = 0 
 
strProcName = "glrIsMember" 
 
'Intialize return value 
glrIsMember = False 
 
'Initialize flag for determining 
'context for error handler 
intErrHndlrFlag = FLAG_ELSE 
 
Set wrk = DBEngine.Workspaces(0) 
 
'Refresh users and groups collections 
wrk.Users.Refresh 
wrk.Groups.Refresh 
 
intErrHndlrFlag = FLAG_SET_USER 
Set usr = wrk.Users(pstrUser) 
 
intErrHndlrFlag = FLAG_SET_GROUP 
Set gru = wrk.Groups(pstrGroup) 
 
intErrHndlrFlag = FLAG_CHK_MEMBER 
varGroupName = usr.Groups(pstrGroup).Name 
 
intErrHndlrFlag = FLAG_ELSE 
 
If Not IsEmpty(varGroupName) Then 
glrIsMember = True 
End If 
 
glrIsMemberDone: 
On Error GoTo 0 
Exit Function 
 
glrIsMemberErr: 
Select Case Err 
Case ERR_NAME_NOT_IN_COLLCTN 
Select Case intErrHndlrFlag 
Case FLAG_SET_USER 
strMsg = "The user account '" & pstrUser & "' doesn't exist." 
Case FLAG_SET_GROUP 
strMsg = "The group account '" & pstrGroup & "' doesn't exist." 
Case FLAG_CHK_MEMBER 
Resume Next 
Case Else 
strMsg = "Error#" & Err & "--" & Error$(Err) 
End Select 
Case ERR_NO_PERMISSION 
strMsg = "You don't have permission to perform this operation." 
Case Else 
strMsg = "Error#" & Err & "--" & Error$(Err) 
End Select 
MsgBox strMsg, MB_ICONSTOP + MB_OK, "Procedure " & strProcName 
Resume glrIsMemberDone 
 
End Function 
 
Bill Paton <a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a> <a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a>

elizabeth · Feb 17, 2000

You can just lock the fields on the form if you are not concerned about people entering data directly into the tables, or through queries, or through other forms.

svegal · Feb 17, 2000

I have Elizabeth's way working in a test case. With some clean up I can make it work. 
If I used Bill's suggestion, Is there a way to prevent users from accessing anything that can change the table except by using the form that has the records locked? 
 
Thanks to both of you for the help.

elizabeth · Feb 17, 2000

Here's a few ways, depending on the sophistication of your users, not in order of easiest or best answer first... 
1) Use Access security to make the table inaccessable to all groups, and put code behind the form to open the table "with owner's permissions". 
2) Lock up the whole database by making it an MDE or using the ODE to shrink-wrap it. 
3) Turn on the table's "Hidden" property. 
 
Have Fun!!

svegal · Mar 13, 2000

I have the form set up with fields locked that already contain data. I also have several filters on the form to help users find the records they need. Everything is looking good and I am securing the database. 
 
How do I set up the form to open the table " with owners permission"? I can find in the on-line help how to do this with a query but not with a form. 
 
 
If I can get through this, I think I will start having fun!!

elizabeth · Mar 13, 2000

If the form locks the fields except to the proper group(s) you can let anyone open the form. Start having fun now

svegal · Mar 13, 2000

I want to secure the database as mentioned in your second posting on 2/17/2000 -- Use Access security to make the table inaccessable to all groups, and put code behind the form to open the table "with owner's permissions". 
 
I can do the first part (use Access security to make the table inaccessable to all groups) but am having trouble figuring out how to put code behind the form to open the table "with owner's permissions". 
 
If I understand this right the users will not be able to open the table to edit it. They will need to access it thru the form.

elizabeth · Mar 13, 2000

My understanding was that you could base the form on a query (also secured as the table) against the table and (assuming the owner is a member of the admin group) run that "with owner's permissions". Let me know if it doesn't work; it's been awhile since I've used that option so it may be more complex than I remember. This would give you the back-end security to just lock the individual fields as WP suggests.

svegal · Mar 14, 2000

Still having trouble but getting closer. 
 
I created a query in design view and put that query name as the record source in my form. Everything worked as long as the user had permission to read update and insert data in the query. If I took these permissions away the user could not add or change anything in the form. 
 
If I grant the user permission to read, update and insert data in the query then they can execute the query and change my table how ever they want to.

Next, I copied the SQL code from the query and pasted it directly into the record source field on the form property sheet. Here is what I pasted in: 
 
SELECT [Drawing Approvals].Drawing_Number, [Drawing Approvals].Revision, [Drawing Approvals].[Production Number], [Drawing Approvals].By, [Drawing Approvals].Checked, [Drawing Approvals].Approved1, [Drawing Approvals].Approved2, [Drawing Approvals].Approved3, [Drawing Approvals].Approved4, [Drawing Approvals].[Approval's Complete] 
FROM [Drawing Approvals] 
WITH OWNERACCESS OPTION; 
 
It works for a user with permissions on the table but not if the user has no permissions on the table. It appears that the WITH OWNERACCESS OPTION is not working when I put the SQL code in the record source field on the form property sheet. 
 
Any suggestions? 
 
Thanks again for your help.

elizabeth · Mar 14, 2000

Sorry, I've led you down a blind alley

I originally set up the following permissions for User group: 
table Table1 - Read Design, Read Data 
form Form1 - Open/Run, Read Design (RecordSource: 
SELECT Table1.* FROM Table1 WITH OWNERACCESS OPTION

Results: as a member of the User group, I could 
open and read but not change data in Table1 
open and read and change data in Form1 
 
BUT!, before I began writing this I wondered if that user could write a query with user permissions against that table (no). In the process of testing, I noticed that user could no longer make changes to the data using Form1! Somehow between multiple sessions of jumping in and out as admin & user, I must not have been set to the all the permissions I applied. When you launch Access it asks for name & password, not when you open the db, so I think it's only reading updated permissions from system.mdw when Access launches, not when the db opens. 
 
So if you waded through all the above, my apologies; this does not work as I thought. Sigh. Depending on how desperate you are not to create separate Approval table(s), I am aware of (but have not used) two other features that might be of help. One is creating a backend database and securing it with a database-level password. I don't know how but you embed the password in your front end somewhere. This keeps people from overwriting your data, but not from linking to it or importing to it. Sounds like you're more interested in keeping it from being changed than in keeping it confidential. The other is to change permissions in code right in the app with the SetPermissions method. 
 
Good luck! Please post if you find a good security solution.

svegal · Mar 24, 2000

The answer for the field level security can be found at: 
 
<A HREF="

http://support.microsoft.com/support/Access/Content/SECFAQ.asp#Item25"

TARGET="_new">

http://support.microsoft.com/support/Access/Content/SECFAQ.asp#Item25</A>

What you do is change the permissions for the record source of your form with code so that when the form is active the user has permission and when the form is deactivated the permission are taken away. 
 
I think I am going to try the first method (linking different tables together) above to see if it works better. If it works the way I think my users will be able to access the data any way they choose. 
 
I'm having fun now.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Creating fields that only certain groups can change 1

svegal

Technical User

elizabeth

IS-IT--Management

WP

Programmer

elizabeth

IS-IT--Management

svegal

Technical User

elizabeth

IS-IT--Management

svegal

Technical User

elizabeth

IS-IT--Management

svegal

Technical User

elizabeth

IS-IT--Management

svegal

Technical User

elizabeth

IS-IT--Management

svegal

Technical User

Similar threads

Part and Inventory Search

Sponsor