Restrict access to recortds

  • Thread starter Thread starter gavin
  • Start date Start date
G

gavin

I am trying to find out how I can restrict users to only being able to
read/edit records which they have entered.

Apparently I need to "limit the recordset for the form to those where the
User ID is equal to the current user". Can anyone tell me exactly how to do
this?


Regards,



Gavin
 
You will need to implement user level security to do that, Gavin. Get a copy
of the Security FAQ, read it several times, and follow it to the letter.
Don't miss a single step. You can find a copy of it on the Security page of
my website.
 
User Level security will restrict users to tables, not row
level.

How are you getting the user's username? By using the
CurrentUser function, or Windows login, or ???


All you need to do is to create a query that says:

Select * from Table where userName= CurrentUser

Base the form off that query.


Chris Nebinger
 
You need a column in the table 'createdby' which holds the person who created the record. Then in your SQL simply code: SELECT .... WHERE createdby = <current user
To be more reliable, I always code
WHERE ucase(createdby) = ucase(<current user>)
to make sure you are always dealing with the same cas
The function to get the current user is available on the internet, do a searcxh for 'fosusername'
 
Chris,
Without implementing user level security, ALL users who open the database
will be logged in as Admin. Thus your query, without user level security,
will pull ALL records from the table, assuming there is a userName field
that has been populated with the CurrentUser.
 
Gavin,

You can use the network's (or PC's) User Name for each person.
Let's say that you have a table called tblCustomers. Create a field in the
table
called UserName. Create a Main Menu (frmMainMenu), as well as TWO forms for
Customers: one for ADDING NEW Customers (frmCustomers_ADD) and a second for
EDITING EXISTING records called "frmCustomers_EDIT". (Make the RecordSource
for frmCustomers_ADD: "Select * from tblCustomers where false";
make the Record Source for frmCustomers_EDIT: "Select * from tblCustomers
where UserName = " & GetUser()

Now, create a text control on BOTH of these forms called UserName (just as
it is called in tblCustomers). Using the Properties for each of the UserName
controls, set Locked to Yes, set the ForeColor to 128, and the TabStop to
No. (This will prevent anyone from changing the data in UserName manually
via the forms.)

Now, in the frmCustomers_ADD form, use this code:
Private Sub Form_BeforeUpdate()
If IsNull(Me![UserName]) Or Me![UserName] = "" Then
Me![UserName] = GetUser()
Else
'do nothing
End If
End Sub

Next, in the frmCustomers_EDIT form, SKIP the above code, since the UserName
should already be in the table and thus on the form where it cannot be
altered.

====================================================
In the form frmCustomers_ADD AND frmCustomers_EDIT "General Declarations"
section, type:

Option Compare Database
Option Explicit

Private Declare Function GetUserName Lib "Advapi32.dll" Alias "GetUserNameA"
(ByVal sBuffer As String, nSize As Long) As Long

Private Function GetUser() As String
Dim sUserName As String
Dim lgSize As Long
Dim lgLength As Long
sUserName = String(15, " ")
lgSize = Len(sUserName)
lgLength = GetUserName(sUserName, lgSize)
GetUser = Left(sUserName, lgSize - 1)
End Function
=======================================================
The result: When you add a NEW customer, the Windows user name for the data
entry person will automatically be entered. In addition, when you open the
other form for editing/viewing, only you will be able to see the records you
entered.

There are many other ways to do this, but the use of the Windows/Network
User Name
is rather handy. I use it in many applications, where I save the:
DateCreated 'using Form_BeforeUpdate()
DateLastModified 'using Form_BeforeUpdate()
CreatedBy 'using the GetUser() function with Form_BeforeUpdate()
LastEditedBy 'using the GetUser() function with Form_BeforeUpdate()
---Phil Szlyk
 
Back
Top