Locking Specific Records

Hi,

Is there a way to lock specific records by using user-level security?

Example: If person A logs in and creates 2 records (1, 2) then if person B logs in, I want person B to have no rights to person A's records (cannot modify/update). But if person A logs in, person A has permission to the 2 records it recreated (but not to other ones).

Is this possible??

Thanks

If both users have read/write permissions to the same table/query it would be programmically difficult if not impossible to sort by records for read only. What I suggest is to add a field that identifies which user has entered the record. Then using a query you can filter by user name to allow read/write to those records they created. (Which should be done anyway) You can create another query as an option to view all records and then set permissions for read only. Then either user can view all the records but will not be able to edit them.

lwells

How can I use the User-level Security Login name. For example, when I create the query to set the read/write permission, how would I put in the Login Name in the If statement for the permission, so that the computer knows who is logged in?

Thanks

Place the following code in a module

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function GetUserInfo() As String
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
GetUserInfo = Left$(strUserName, lngLen - 1)
Else
GetUserInfo = vbNullString
End If
End Function

Next place a field in your table that will store the user name. In the form that you use to enter new records place this function as the default value =GetUserInfo() in the control.

Now when the user enters a new record, by default it will place the name of the user into your table for that record.

In your query under the field for your user name, use the same GetUserInfo() as the criteria to sort records by.

When the user logs in, only those records they created will be available.

lwells

Thanks!! But sorry, What's a module? is it one of the sections in VB coding?

And also, this is using User-level security right?

First question. A module is where specific procedures are stored. With in the module will contain 7 other windows each with there own specific functions. The window that is most often used is the one where the procedures are typed in. Each form and report can have it's own module in which procedures that are written will only effect that particular form or report. These are generally private modules. There is also a public module in which procedures written can be used everywhere in your application. And there is also a class module for creating procedures using a class instance. So the above code can be placed in a form or report module and it will be private and can only be used by that form/report. However if you place the code in a public module then it can be used by queries, forms and reports anywhere in your application.

Second question. The code will retrieve the network login name that was assigned by the Network Administrator. Or it will retrieve the name that the user has put in place of what the administrator assigned when he starts his computer up. Now if you want to actually get to the security .mdw and get the name from there, then there is different code used for that. This will require that you set up which group you will search by in the procedure and then compare the user name against that security file, which is a bit cumbersome in my opinion. The simple CurrentUser() function that is already built into access will automatically give you that info without code. If you want to test that procedure, make a form and place an unbound text box on it. Set the default value to =CurrentUser() and open the form. Sign on to your database using a different name from the list you have added to the User Level Security and the text box will show that name as the person who logged in. That maybe alot simplier if that is all you are needing. You can use the CurrentUser() function just as easily as using the login name through the network. I personally use the network login so that users don't have to log into the database everytime they want to add or view a record. (Helps cut down on users signing into the database and then leaving it open to avoid the hassel of retyping in the security window each time)

lwells

Hi,

I don't know any VBA codes but does the code you gave me suppose to separate into two "sections" (or modules)? because

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

is separated with the rest.

what do you mean by: "place this function as the default value =GetUserInfo() in the control." There's a "Default value" in the properties and there's a "control source". Which one should i put it under. (because the VBA code is giving me errors, i couldn't try them out)

Also, so I have to create two queries. One with the GetUserInfo() criteria and the other without criteria? So the form that the user uses to input data is based on the query with the GetUserInfo () criteria... and the other which is suppose to be read-only without the criteria? How do you set the permission as read-only or updateable...because all records needs to be read-only and only the ones the user created is updateable. I am so confused..

I've put in the =CurrentUser() thing in my form. Can i use that as a criteria for my query?

Sorry for so much questions. Thanks so much for your help.

For your first question, yes the Private Declare is separated from the rest of the function call. The entire code that I gave you needs to be placed in a separate module not in a forms module. When you open the code window from a form, you will see an icon at the top (usually directly under File in the tool bar) with a small drop down arrow. When you place your mouse curser over the icon a text tool tip will display Insert Module. The drop down will give you three options Module, Class Module, Procedure. You would select Module and a blank module will open up with a code window. This is where you will place all the code I gave you. After you have pasted the code, click the Save button. It will prompt you for a Name to save the module as (By default usually Module1 if this is the first module) and you can give it what ever name is appropriate. Then press Debug/Compile and compile your database for any errors.

Then with your form control, you would use the default value not the control source for the control and type in =GetUserInfo(). This will then find the users name that logged into the network and display it as the default text in the control.

If you wanted to give all users the permissions to view all records without being able to edit them, yes you will need a separate query without any criteria and give that a read only in your security permissions to the group or individual users. You won't be able to set permissions by records, only for the entire query. That was the reason for using a query to only display what the records for that user so they can be edited, yet hidden from view by others. No permissions would be needed on this type of query, but unfortunately, users can't see the records entered by others. So yes, one query with the criteria by users for editing and adding, and another query with no criteria that is set to read only so all the records can be viewed, not edited or added.

Second question using the CurrentUser() in a query, yes you can, just omit the = sign in front. Then your query will sort records by the CurrentUser that has logged into your database not the user that logged into the network.

Hope this cleared things up a bit.

lwells

wow!!.. you're a genius!!.. you helped me a lot.. it's working super well now. Thanks so much!!

Is there a way for "Admin" group to edit records (fields in the record) if the record is locked (fields in the record are locked)

becuase i have two queries... one that only displays the records depending on who is logged in... and the other having no filters (but locked.. = cannot edit fields)..

Is there a way for a user that is in the "Admin" group be able to edit ALL records in the database? (even the locked ones?)

Thanks

Hi, for the above problem, I used another form (FormAdmin)... and I placed a button that opens this form on the startup form (FormStartup)... I put this criteria before the open action in VBA

If CurrentUser() = "leepn" Or "Jane Zhao" Then

It gives me an error saying "Type mismatch".. Why is it doing this?

The reason I want "Or" in the middle is i want multiple user to open it but others can't (example: leepn and Jane Zhao can open the form.... but not Karen, Marcy etc.)

Thanks

For your first post "Admin" has full rights to every object in the database. So the users you added to "Admin" will have all rights to all objects in the database. The query itself is not locked, only what permissions were established to that object for each group or individual. So if Karen was given read only permissions to a query, then Karen can only read and not modify records. However Admin still has full rights to the same query.

In your second post, just change your code slightly:

If CurrentUser() = "leepn" Or CurrentUser() = "Jane Zhao" Then

you can also use this syntax as well

If CurrentUser = "leepn" Or CurrentUser = "Jane Zhao" Then

without the ().

lwells

Is it possible to lock specific records once entered. ie when the sale is finished, I would like to lock that record and all related to it so any details can't be changed.